Mysql – How to join different tables with same propriety tables

database-designMySQL

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:

+--------+---------+-----------+
| ID_MEN | ID_DESC | ID_IMAGES |
+--------+---------+-----------+
| 1      | 2       | 3         |
+--------+---------+-----------+

Best Answer

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

Related Question