Hello, Everyone.
i am confusing that how to store Child and Parent Categories in tables in mysqli database with proper normalization.
** i have child categories which are child of almost all parent categories.**
What i do is?
i create two tables
1.for Parent Categories
2.for child Categories
3. for storing child category and parent id used in it for example
child_id – parent_id
1 ------------ 1
2 ------------ 1
This is the formate, but this way its difficult to manage in some case while displaying
also i have another way in which i dont use 3rd table and in 2nd table (child categories)
i store child category and parent category but this way child categories duplicate in a database child table.
i am confusing and i want to know your opinion and if you have anyother better way then i really appriciate it.
The terms to look for are hierarchical database and self-referencing database. They can be very difficult to understand, especially things like querying for them and showing them. Quit often tree views are not too difficult but you might need to spend time getting your mind to understand how to create a tree view of hierarchical data.
They are often called self-referencing because each record in the table is related to other records within the same table, except for the top level.
One of the most advanced, certainly the most complicated, topics is detection of the error where (I will use the term category here) a category has as a sub-category a parental category. I forget what it is called but I could find it if I need to. I know that a guy at an aircraft manufacturer was paid big bucks because he was the developer and maintainer of a program that could detect such problems in the engineering design of aircraft. Since then the internet has developed and now it is easy to find answers but it is still quite complicated.
I think the articles listed in the previous searches will help. Please read some of them. I won’t say anymore but you might have questions after reading the articles.
I should have said hierarchical table and self-referencing table.
I have not seen the term adjacency model used, either in this context or for anything. The Adjacency list - Wikipedia article is very theoretical but it seems that adjacency model is more generalized than hierarchical data.
Is that your website? When I post relevant useful links to my website here (as you did for your website, correct?) I am accused of spamming. I am not criticizing you or anything like that, just saying that the moderators around here seem inconsistent.
(Pinch of Salt: I am no longer a member of the moderation team.)
Well, #1 Rudy’s been around the forums longer than most of the moderators, and is considered an authoritative source on the matter of Databases. So yes, his links, even self-referential ones, are treated as trusted. It’s also rather rare that he actually puts one in his posts.
#2: Said link was linked to by another user before he even managed to, so I doubt anyone could claim he was spamming unless they wanted to try and imply that he was using more than one account.
#3: The Adjacency Model is a fairly standard naming for it, and has been for certainly no less than half a decade. The ‘hierarchical data’ version you refer to is called the Nested Set Model.
Okay well that is another term to search for for finding relevant articles. In my opinion articles about the Adjacency Model tend to be more technical.
I had not seen nor have I used the Nested Set Model. I do not know why you say I said anything about it except I might have been ambiguous.
I don’t understand the relevance of that.
The implication being that it is a matter of the quality of the material but the matter I described is that of spamming, not a lack of quality. I apologize for causing this discussion to go off-topic so I will just say that I am interested in an official policy on the subject. If the quality is judged differently depending on whether the author is making the post then … well that can be discussed elsewhere.
@r937, I’m a huge fan of your Simply SQL book and have had the categories link saved in my bookmarks for awhile. Your book really helped me actually understand SQL. I’m always happy to refer friends, colleagues, and internet strangers to your writing.
In my professional opinion without more detail about business requirements the pattern with best balance between simplicity and flexibility is adjacency list.
This article provides several patterns that can be used.
