Which would be the best solution to join 4 different tables with X tables that has to be the same for the 4 tables?

Let's presume we have 2 tables MENU and PLATES each of that tables could be joined with tables like IMAGES, DESCRIPTIONS, ALLERGENS and other tables that will give some properties to MENU and PLATES

Which would be the best practise to join those property tables with the main ones?

Should i create in each property table a column for each main table as foreign key? Like this:

| ID | DESC         | ID_MEN | ID_PLATE |
| 1  | PIZZE        | 1      |          |
| 2  | CHICKEN SOUP |        | 4        |

Or should i create a intersect table for each main table that has FK for each propriety table like this:

| 1      | 2       | 3         |
  • Can you help me understand the difference between a MENU and a PLATES record? In my mind those seem to be the same thing but I would imagine you have a more granular definition, which is why they are 2 separate tables. Also, are the other "attributes" (IMAGES, DESCRIPTIONS, ALLERGENS) a 1-to-1 relationship or is a MENU/PLATES object allowed to have multiple of each? (I am going to guess that DESCRIPTIONS may be a 1-to-1 where the others could be a 1-to-many relationship) – Kirk Saunders May 15 '20 at 13:51
  • @KirkSaunders the MENU / PLATES could have multiple attributes so 1-to-many, the tables are separate as i'm working on a yet created database where a MENU has almost same columns of PLATES but are separated as some columns are different and actually the PLATES belong toMENU but each PLATE or MENU could have 1 or more images 1 or more description (different language) and different allergens like PLATE "pizza" could have allergens "pasta", "pomodoro" or a MENU could have some generic allergens – Igor Mytyuk May 15 '20 at 14:01
  • Awesome! Thank you for that information. One more question, does that mean that the PLATES is a child table to MENU? Or are they just separate tables which hold similar but different elements? – Kirk Saunders May 15 '20 at 14:09
  • @KirkSaunders in this case PLATES are child of menu but there is another table ITEMS that is the same of PLAYES which hold similar elements but doesn't belong to any MENU (the table ITEMS) should have same proprieties as MENU and PLATES like images etc.. – Igor Mytyuk May 15 '20 at 14:14

I think that a combination of the two solutions you provided is what you are looking for.

I think specifically what you are wanting to do is create a separate table for each of your attributes (IMAGE, DESCRIPTION, etc...). Then you will create a bridge table between your objects and their attributes. This allows you to have tables for each of your attributes. That way you have those items normalized. The bridge table then allows you to maintain the 1-to-many relationship between each of your objects and its attributes. The downside with this plan is you will end up having a lot of tables as your attributes increase or your object types increase. But the separation of data elements will allow you to keep the data integrity much better.

There is another solution utilizing a generic OBJECT-ATTRIBUTE table that allows you to maintain everything now and in the future in one place with a little bit of effort. There is a great article provided by Red Gate which recommends against this method. I would check out the "One table to hold all domain values" section.

Here is what I think you are looking for. Assuming:

  1. PLATE is a child table of MENU
  2. ITEM is a child table of PLATE
  3. The only 3 attributes you currently have are DESCRIPTION, ALLERGEN and IMAGE

Let me know if there are any questions or if this solution does not take care of everything you are looking for.

Possible ER Diagram

  • First of all thank you! Actually that was by biggest fear that there will be lot of tables and assuming that the "property" tables will be up to 8 yea.. but as you said it's important to keep the data integrity so i'd go for that bridge table solution – Igor Mytyuk May 15 '20 at 15:05
  • This smells like "over-normalization". – Rick James May 16 '20 at 17:30
  • @RickJames, I am starting to realize I might "over-normalize" as a general tendency. What changes would you make? – Kirk Saunders May 16 '20 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.