Apologies if this isn't the right place for this kind of question but I felt sure you guys would know the answer to this fairly simple design question.
I would like to model an item and a subject entity.
An item can be assigned to zero or at most one subject.
A subject may consume zero or one item.
How would it be best to model this kind of relationship in the database? I was originally thinking of having an item FK in the subject table but that would potentially allow multiple subjects to be assigned the same item. Then I was thinking
of having a subject FK in the item table that would force only one subject to be assigned 1 item.
I think I'm just going round in circles here and it may be that my business logic should take care of these issues.
My question is how is it best to model this kind of relationship?
View Complete Post