Common `event` (datetime) table for say a manufacturing DB, an anti pattern

database-design

There are many different types of events that can happen during a manufacturing process.
E.g.

  • Built Step1
  • Build Step2
  • Test
  • Checked into repairs
  • Checkout out of repairs
  • Packaged
  • Dspatched, etc.

I see two ways to model each step:

  1. Have separate independant tables. Each table with its own timestamp field.
  2. Extract the timestamp field from the tables, and place it in a common event tables which has say 2 fields:
    • timestamp field
    • A column which links it to which type of event it is (which table has the specific details for that type of event).
    • Could also have a user field to track who did what etc.

Any reasons why not to use way 2 (event table)? It seems advantages for running reports on what events happened during a certain time frame. A way of uniting events in the factory. But my research seems to always point towards avoiding any common function tables.

Best Answer

Having an EVENT table sounds reasonable, particularly if there is a set of attributes common to all events. I would tend towards a more descriptive table name, perhaps MANUFACTURING_EVENT (or some terminology used by people in that industry). I would then have detail tables for specific manufacturing events with a FK relationship to the parent.

Related Question