Mysql – How should I model the database – cannot decide on multiple options

database-designmariadbmariadb-10.4MySQLoptimization

I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.

The basic idea:

I have a Table called Article in which I want to store simple fields such as name (e.g. varchar), description (e.g. varchar), …, etc. also more complex information like Producer, Price and so on.

I have worked out the following:

Implementation I (using just a FK)

Table: Article
| id (auto_inkr.) | fk_producer | ... |
| --------------- | ----------- | --- |
| 1               | 1           | ... |
| 2               | 1           | ... |
| 3               | 2           | ... |
| ...             | ...         | ... |

As defined, Producer(id: 1), is the Producer of Articles (1, 2, ...?).

Implementation II (using a lookup table)

Table: Article
| id (auto_inkr.)   | fk_article_producer | ... |
| ----------------- | ------------------- | --- |
| 1                 | 1                   | ... |
| 2                 | 1                   | ... |
| 3                 | 2                   | ... |
| ...               | ...                 | ... |
Table: Article Producer
| id (auto_inkr.)   | article_fk  | producer_fk |
| ----------------- | ----------- | ----------- |
| 1                 | 1           | 1           |
| 2                 | 2           | 1           |
| 3                 | 3           | 2           |
| ...               | ...         | ...         |

In this case here, I have implemented a mapping table. This would also allow me to have a many-to-many configuration. I don't have to implement it that way – it is up to my backend-code how I store the data.

Questions

Please keep in mind, that my Article table as of now will have like 7+ dependent tables (as shown in the example here: e.g. Producer). I want to design it, so I can write an efficient filter-function (query) for my text- respectively tag-based-search later on.

  • Which design should I choose and why?
  • What are the benefits or downsides of these approaches (best practice)?

Best Answer

Design 2 two is needed when here are multiple producers per article, you need a bridge table between article and producer, so a producer can have multiple articles and an article can have multiple producers

your first is only valid for 1 to 1 reöationships.

so what you you t have 1:1 or m:n

Thet will determine, what you need