Database design one to many where many is at least one

Question

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

Answer 1

I don't think that there is ever a case where you would want/need to contain a reference in two seperate areas.

Also, you want to have the reference on the many side of a one to many relationship. If there are 1+ B's for an A, then the B table should contain an A reference, not vice versa.

If C needs to return the B's that A has, just make C a stored procedure or a view that joins the A and B table, that way the data can never get out of sync.

Answer 2

Maybe I'm not understanding and oversimplifying, but if there is a Primary key/foreign key relationships set up between B and A and the fk field with the id for B that is in table A is a required field, it won't let you add a record to A that doesn't have a record in B. If it is more complicated than that you may need to enforce the rules through a trigger on A.

Answer 3

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).