SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let's say every weekly pay period statement is expected to have seven daily entries.)
Is there any way to maintain a constraint like this at the database level?
The best I can think of would be to have n foreign keys on the one, so in our example the weekly statement table would have columns for day_0, day_1, day_2, etc, which seems uncommon and possibly not 1NF.
This question is inspired by a recent meme of "making impossible states impossible" in the Elm programming language community.
Best Answer
Another level of indirection to the rescue!
Add a Week table, containing the date of the first day of the week and whatever else seems sensible (week number, Year, etc), with one row per week. The Paycheck table has an FK pointing to the Week table.
Add a Day table. This has one row for each day, and contains the Date and an FK to the relevant Week table. It might also have indicators that this is a weekend or national holiday, and so different pay-rates might apply.
Add a table PaycheckDay. This has FK's for the Paycheck row and the appropriate Day row. These two FK's should also be a unique non-clustered index. Pay details for each day are stored on this row. For extra security there could also be constraint that the Day belongs to the same Week that the Paycheck does (also this might be enforced by a Trigger rather than a constraint depending on physical database type).