0

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)?
2
  • 1
    What is an Article in this context and what is a Producer?
    – bbaird
    Jan 21, 2021 at 17:30
  • An Article is a physical, sellable Product that you can put in the basket of an e-store. In this context. The Producer is just the manufacturer.
    – F. Müller
    Jan 21, 2021 at 20:23

2 Answers 2

0

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

3
  • Yes, that is what I already figured out. But the question remains: "Can I" or rather "Should I" use design 2 for all of my tables? Regarding querying and performance - would it be better to have the relationship between the two tables in a separate table in every case for a faster lookup or is this just nonsense? Do you get what I mean by that?
    – F. Müller
    Jan 21, 2021 at 20:27
  • 1
    look i don't knpow what reölationship ypu have, so my answer give you the option what to do solution 1 for 1:1 or solution2 for m:n. a briidge table for 1:1 relationships a possible but bring no further information
    – nbk
    Jan 21, 2021 at 20:30
  • The relationship is 1:n (1 Producer -> n Articles). I was just overthinking it, I guess. You are right it does not really add any value. At first, it looked like one may be able to write a more performant query in this case (because you can isolate the essential part for the lookup), but you end up with joins or subqueries anyway.
    – F. Müller
    Jan 21, 2021 at 22:10
0

Make a table for each "entity", such as "article" and "producer". Each such table would have an id and a variety of attributes (name, etc).

Then characterize the "relationships" between pairs of those entities and build relations:

  • For one:one, rethink why you have two separate tables; consider merging the tables together.

  • For one:many, the "many" table needs a column with the id of the "one". And have an INDEX on that column.

  • For many:many, you need an extra table with only two columns, namely an id into each of the other tables. (Note: it is a waste to have a new id for this column. More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table )

A FOREIGN KEY does two things: it provides a constraint that checks data integrity, and it makes sure there is a suitable INDEX (for performance). (Foreign keys are desirable, but not required.)

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.