Search code examples
databasedatabase-designdenormalization

Table design help: Need multiple different entities pointing to an an entity grouping

I can't for the life of me get this through my head. It seemed so simple at first and now I've stared at it for too long, I can't see the forest for the trees.

In essence we have Day divisions, these are abitrary... 

ID  Name    Start  End
1   Name1   1300   1400
2   Name2   13:30  19:30
3   Name3   20:00  21:30

etc

We want to be able to configure the days of a week with any combination of these day divisions, so we have the WeekConfigurations table

ID  ConfigurationID  DayOfWeek   DayDivisionID   Order
1   1                1           1               1
2   1                1           2               2
3   1                2           2               1
4   1                2           3               2
5   2                1           1               1
6   2                3           2               1
7   2                3           3               1

Now we want other entities (Person, Office etc) to have a certain week configuration assigned to them. And this is where I'm coming un-stuck. The configurationID I want to assign isn't the primary key so I can't enforce the referential integrity between the entities and a configuration.

Can anyone point me in the right direction? Something about the WeekConfiguration table smells funny to me, but I just can't see it.

PS: Sorry, I had a nice diagram to illustrate but I'm a newbie and can't add it

Solution

  • At the logical level, you have a "configuration" and a set of "configuration items" under that configuration. So instead of just one configuration table, use two:

    enter image description here

    In this design, the ConfigurationId is a primary key (of Configuration table), so you can directly use it as a parent endpoint of foreign keys.

    The ConfigurationItem PK ensures a single day division can be used in at most one day of week per configuration and the alternate key U1 ensures it is unambiguously ordered (within the same day of week and configuration).

    I have also chosen to avoid a surrogate PK in ConfigurationItem. You can easily add it (and make the existing PK alternate) if necessary - e.g. if you have additional FKs you didn't mention or use an ORM that doesn't play well with composite PKs.