Database modelling for 2 many-to-many relations that have their own one to many relation - Stack Overflow

With reference to the attachment, data has a many to many relationship with both entities. What would be the right thing to do?

To add more context, each data entry can have 0, 1, 2, ..., N categories, where N is the number of categories in the category table. For each category an entry has, it can have 0, 1, 2, ..., N_c sub categories associated with it, where N_c is the number of sub categories a category has.

I have thought about making one joining table between the data and sub category since each sub category will also tell me the category, but this is susceptible to having a data entry that has a category but no sub category - which is possible.

Is having 2 joining tables the best thing to do? Would you consider this approach to have some form of data redundancy since we some of the categories if we have sub categories

With reference to the attachment, data has a many to many relationship with both entities. What would be the right thing to do?

To add more context, each data entry can have 0, 1, 2, ..., N categories, where N is the number of categories in the category table. For each category an entry has, it can have 0, 1, 2, ..., N_c sub categories associated with it, where N_c is the number of sub categories a category has.

I have thought about making one joining table between the data and sub category since each sub category will also tell me the category, but this is susceptible to having a data entry that has a category but no sub category - which is possible.

Is having 2 joining tables the best thing to do? Would you consider this approach to have some form of data redundancy since we some of the categories if we have sub categories

• database
• many-to-many
• data-modeling
• entity-relationship
• modeling

Share Follow asked Jan 30 at 14:11 Jackanap3sJackanap3s 5955 bronze badges 2

• I may miss something but why exactly does data have a subcategory but no main one? I try to think of a case but can't come up with one. Besides that i think 2 Joining Tables are the way to go here. Otherwise you to seperatley query all the time data thats not covered by a table. this is tedious – LMA Commented Jan 30 at 14:31
• @LMA yes you are correct but I think I have something that works. I'll upload my solution. – Jackanap3s Commented Jan 30 at 15:05

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

After some more thinking, I think this is the best solution.

This eliminates a data entry from having sub categorisations without a main categorisation.