Search code examples
databasedatabase-designschema-design

Database design one to many where many is at least one

Somewhat related to my previous question, this is regarding creation patterns to enforce table patterns where 'A' has many 'B' children where 'C' is the table of child 'B's for 'A', but has at least ONE.

Currently I have:

A (A_ID primary key, B_ID reference key)
B (B_ID primary key, etc)
C (A_ID reference, B_ID reference)

The point is that A definately always has at least ONE 'B' 'child', but optionally many more.. however the problem I have is that the 'C' table could currently reference the same 'B' that 'A' is already implicitely references..

example:

A
- Id: 1
- B_Id: 37

C
- A_Id: 1
- B_Id: 37

Whats the best way to constrain this? noting that 'A' could be updated to attempt to reference a 'B' that is already stated in the 'C' collection for that 'A', and more likely, 'C' references a 'B' that is already stated implicitely by the related 'A'..

Hope that makes sense, and again cheers in advance.

Edit: the tables are as follows:

'A' is a submission, a submission can have many 'contributors' (members), but always at least one. 'B' is a member 'C' is a table that links a 'A's to many 'B's

Solution

  • Translating your abstract schema into a concrete schema I believe looks something like this:

    • Submission (SubmissionID, PrimaryContributorID, ...)
    • Contributor (ContributorID, ...)
    • SubmissionContributors (SubmissionID, ContributorID)

    This can work to give you the "at least one" contributor per submission, but it means having some odd/difficult rules to enforce. The difficulty exists because of the PrimaryContributorID -- Does a matching record exist in the SubmissionContributors table? If the PrimaryContributor changes, do you have to rearrange records in SubmissionContributors? If the PrimaryContributor doesn't have a matching SubmissionContributor record, every time you list contributors for a submission you have to union in the PrimaryContributor, etc.

    Unfortunately SQL doesn't have an "at-least-one-to-many" relationship (not that I know of anyway :) ), so any solution you come up with is going to involve some compromise.

    For me, the best approach would be to remove the PrimaryContributorID; All contributors exist in the SubmissionContributors table and you will have domain logic that enforces the "at-least-one" part of the requirements (eg, methods that insert/update submission records will fail if there is not at least one contributor, and methods that read submission records will fail if there is not at least one contributor).