Relational DB design: Should I skip a table with only 1 column that create relationship between 2 other tables?

Question

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.

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

                   OR

OPTION #2
----------       --------------------
|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 ?

Answer 1

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?)

Answer 2

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.

Answer 3

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.

Answer 4

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.