Database Modelling - Conceptually different entities with near identical fields
- by Andrew Shepherd
Suppose you have two sets of conceptual entities:
MarketPriceDataSet which has multiple ForwardPriceEntries
PoolPriceForecastDataSet which has multiple PoolPriceForecastEntry
Both different child objects have near identical fields:
ForwardPriceEntry has
StartDate
EndDate
SimulationItemId
ForwardPrice
MarketPriceDataSetId (foreign key to parent table)
PoolPriceForecastEntry has
StartDate
EndDate
SimulationItemId
ForecastPoolPrice
PoolPriceForecastDataSetId (foreign key to parent table)
If I modelled them as separate tables, the only difference would be the foreign key, and the name of the price field.
There has been a debate as to whether the two near identical tables should be merged into one.
Options I've thought of to model this is:
Just keep them as two independent, separate tables
Have both sets in the one table with an additional "type" field, and a parent_id equalling a foreign key to either parent table. This would sacrifice referential integrity checks.
Have both sets in the one table with an additional "type" field, and create a complicated sequence of joining tables to maintain referential integrity.
What do you think I should do, and why?
