0

I am in the process of building out a website and though for years I have organized data a certain way I wanted to see if there was a more efficient way of doing it.

For this project I have (book) genres, sub genres and sub-sub genres. I was, and usually with three tiered categories do, going to build one genre table with the fields 

id(int primary AI), genre(Varchar 255), subgenre(int default 0),subsubgenre(int default 0)

With this if subgenre and subsubgenre were 0 then its a genre ( top tier, parent whatever you want to call it ) if subgenre has a number in it then it is the number of the parent genre and this would be a sub genre of that parent, if subgenre and subsubgenre have numbers then its a sub genre of that sub genre and a subsub genre of that genre, confused yet?

So Im wondering if I should just make a genre table, (id, genre) and a reference table to organize it. This way if they ever want to make a subgenre appear as also a genre the option is there ( right now they say no but I have been doing this too long to think that theres no way they will change their mind ).

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Webtect
  • 819
  • 2
  • 10
  • 31
  • 1
    What about using a parent <> child structure. Can you complicated, but works well. id, genre, parentid – Anthony Horne May 07 '14 at 20:42
  • Hi @AnthonyHorne thanks for responding so quick. What about the third tier? This would work fine if there were two tiers but the third one throws that off I would think? – Webtect May 07 '14 at 20:44
  • 1
    Parent child relationships uses hierarchical queries (not presently available in mySQL) making recursive queries more complex. The design is simpler, but the development is harder. – xQbert May 07 '14 at 20:44
  • 1
    It would cater for n-level (unlimited) hierarchies. You will always be referring to "it's parent", i.e. the first one can be null (or -1 or something non-valid), thereafter, any children you add just refer to their parent's id. – Anthony Horne May 07 '14 at 20:44
  • @xQbert I was just looking for that, but was not sure it was supported in MySQL. Hierarchy column types are head-hearting IMHO, but works very well if implemented. – Anthony Horne May 07 '14 at 20:46
  • 1
    Think of it this way. BookID 1 is related to GenresID 3. GenresID 3 has a parent of GenresID of 2 which has a parent GenresId of 1. so by it's own nature it's in the 3rd level. If it only had 1 parent it would be in subgenre if it had no parents it would be at the genre. This is a MUCH more efficient design for data storage as it eliminates problems with expansion and data repitition. However queries get more complex. If you know for a fact you will NEVER have more than 3 levels, then it can be coded as 3 self joins. With proper indexing data maintenance is far easier. – xQbert May 07 '14 at 20:47
  • If you want to learn some fun sql, you can look at CTE table to recursively "flatten" out the parent child structure. Don't ask me how it is done, I have to google and struggle the CTE recurs every single time :) – Anthony Horne May 07 '14 at 20:49
  • Simply put there's a reason why people tell you to design for 4th normal form and develop for 3rd. Your current design missed 4th normal as genere data gets repeated for each sub. Maintenance of data gets harder; and it's why Parent child relationships and hierichal data exists. Books/Genre, Org charts, Department structures, it's all hierarchical data which parent child support the best IMO. – xQbert May 07 '14 at 20:54
  • So id, genre, parentid. If parentid is 0 then its a top tier. Else if there is a number in there then I would have to look up that row and see if there is a number in that rows parentid which would tell me if its a sub or a subsub. Wouldn't the queries get pretty intricate? How would I keep this from slowing things down if I was to build out a search function to rip through all of these. All in all there are about 300 total in all three tiers which isnt a ton. – Webtect May 07 '14 at 21:00
  • again if you know at most you will only ever have 3 levels. Then it's 3 outer joins and your done; and your data is presented in the format you listed in your question. If however, you anticipate scaling, then you have a problem. http://stackoverflow.com/questions/764497/flatten-adjacency-list-hierarchy-to-a-list-of-all-paths shows examples on the queries. – xQbert May 08 '14 at 17:58

0 Answers0