I must design a database that store items that can be associated to a production. Production is only a identifier with no common attributes. But, a production may or may not have attributes (which are heterogeneous according to the production).

If I use a 3 tables design, I can put a foreign key on the prod_id of the Item and ProductionData tables, but I cannot see the value of it. Is there any ?

If I skip the table, I got 2 independent tables linked artificially by a prod_id, so 2 independent process can manage them.

---------------       ---------------       ----------------------
|Item         |       |Production   |       |ProductionData      |
|id (pk)      |       |prod_id (pk) | <---  |prod_id (fk,ak1.1)  |
|...          |       |             |       |attribute (ak1.2)   |
|prod_id (fk) | --->  |             |       |value               |
---------------       ---------------       ----------------------


----------       --------------------
|Item    |       |ProductionData    |
|id (pk) |       |prod_id (ak1.1)   |
|...     |       |attribute (ak1.2) |
|prod_id |       |value             |
----------       --------------------

Am I missing something or option #2 is a better option ?

  • Is this a many-to-many relationship or a one-to-many? If it's a one-to-many relationship, you don't need the linking table. – Robert Harvey Apr 1 '19 at 16:01
  • @robert: both relationship are one-to-many (a production has many items and many data) – DoogQc Apr 1 '19 at 17:20
  • Then you need the linking table (the Production table). – Robert Harvey Apr 1 '19 at 17:25
  • @RobertHarvey: Why? – DoogQc Apr 1 '19 at 17:30
  • Because that is how you model a many-to-many relationship between two tables. You can't do it without the linking table. – Robert Harvey Apr 1 '19 at 17:33

I'd go with the three tables design. Currently, I see two reasons:

  • It allows you to use foreign keys for production, so that deleting the production can enforce the deletion of all items and attributes.
  • It is extensible: if you need to add static production attributes to your datamodel later, you can do so in the production table. (Maybe, some attributes are already static and could be pulled out of the "attributes" table?)
| improve this answer | |
  • Whoever voted this down: I'd be glad about some feedback, why do you think that this answer is not useful? Thank you! – fxnn Apr 3 '19 at 15:18

So what you have draw there is a one-to-many relationship, in other words, if each production has a set of items that are produced, but those items are only produced in one production, then you could get rid of the production table.

If, however, you have a set of items that can appear in more productions than one, you will have a many-to-many and you will need to map the items to production runs. As in:

---------------       ---------------       ----------------------
|Item         |       |Production   |       |ProductionData      |
|item_id (pk) |       |prod_id (fk) | <---  |prod_id (pk)        |
|...          |------>|item_id (fk) |       |attribute (ak1.2)   |
|             |       |             |       |value               |
---------------       ---------------       ----------------------

This way the same item could be produced in multiple production runs and a give production run can have the same items.

| improve this answer | |

You are correct that you do not need a table with one column.

The reason you are having trouble with your design is that your prod_id belongs to a production, not an item. The item should not have a prod_id field.

The exact structure you need depends on whether an attribute of a production is associated with just a production, or is associated with both an item and a production.

If attributes are associated with the production as a whole, not individual items:

Item: item_id (PK), ...
ProductionAttribute: [prod_id, attribute] (composite PK), value
ProductionItem: [item_id (FK), prod_id (FK)] (composite PK)

This links attributes to productions, and items to productions, separately.

If attributes are associated with productions and items simultaneously:

Item: item_id (PK), ...
ProductionItemAttribute: [prod_id, item_id (FK), attribute] (composite PK), value
ProductionItem: [item_id (FK), prod_id (FK)] (composite PK)

In either case you need the ProductionItem associative table to represent the many-to-many relationship between production and item.

| improve this answer | |
  • The problem I see with this design is that some items could not be associated with a production. Which is something that I want to enfore. My case is the first one you described. – DoogQc Apr 1 '19 at 20:17

I would stick with the three tables, as conceptually its a better fit. Look at it this way:

      | Production  |
          |       |
          |       |
          |       |
          v       +------v
+---------+--+        +------------+
| Production |        | Production |
| Items      |        | Data       |
+------------+        +------------+

If this represents whats really going on, then a Production is its own discrete 'thing' that Items and Data depend on. This is a very common data schema. Think of an invoice. An invoice has an ID, and then a list of items attached. That'd almost always be implemented with a separate invoice table.

Also, from a practical perspective, by maintaining a Production table, you can then use an auto-incrementing key on the Production table to generate Production ID's. That is something that would be possible but a bit awkward with a 2 table setup.

| improve this answer | |
  • This is exactly like that. But since Production is nothing else than an ID here, I'm still wondering what are the advantage of this table. Production ID is already a natural key, so I don't need auto-incrementing key. – DoogQc Apr 1 '19 at 19:58

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.