I'm not really a database novice but I'm not an expert either.
I'm having this difficulty on designing my Post, Category and Tags relationship.
I've read Normalization many times but could not fully digest them and use it as a guide to what I'm trying to achieve. I've also encountered EAV, read about it and likewise. Or maybe I just can't choose which to use for this scenario.
I have few solutions in mind but I could not settle on one. I'm craving for the "BEST" choice. I'm aiming to be able to understand and design good databases!
I have a Post table, like in any other website like wordpress with post capability.
I want that post to be associated with 1 or more Categories and Tags.
I want the category to have a hierarchy where categories like Cats and Dogs are under Animals category.
Categories id -- PK pid -- FK to self (id), allow NULL name description slug Tags id -- PK name description slug Cat_Rel id -- PK pid -- FK: Post Id cid -- FK: Category Id Tag_Rel id -- PK pid -- FK: Post Id tid -- FK: Tag Id
Notice how those Categories/Tags and their relationship with Post are very similar?
I have this urge that I think I could just combined them which leads to my problems.
PostGroup -- I don't know how to call this currently but this should work for now id -- PK pid -- FK: category use only so NULL for all tags name description slug type -- and we add a type where we could explicitly tell if it's a category or a tag Group_Rel -- Now we only have 1 relationship table id -- PK pid -- FK: Post Id gid -- FK: Group Id
So, which one should I follow? Or none of those? Other options?
On your reply, can you include DO's and DON'T, PROS and CONS.
Anything that you think I need to know, understand and realize.