CODEWITHSUNDEEP
database-designdata-modeling
dewyze
dewyze

Reputation: 979

Modeling explicit 1 to 2 relationship in database

I am curious if I want to have a 1 to 2 relationship, what is the best way to model it. There is NO chance that the 1 to 2 will change. It will have a subject that belongs to a pair, so there is a pair object which will always have subject1 and subject2. Is it bad form to have foreign keys for subject1 and subject2 in the pair object? I realize a join table would work just fine, but a subject can belong to more than 1 pair and if I already have the pair object, it would be easier to just grab subject1 then needing to search a whole join table? Or is this just bad data modeling

Upvotes: 0

Views: 182

Answers (1)

ElliotSchmelliot
ElliotSchmelliot

Reputation: 8362

If you know that a Pair will only have 1 to 2 Subjects and you are really... REALLY sure that this will never change, then you can do it this way.

That being said, this is poor design for a couple of reasons. Normally, if you have fields of the same entity type (i.e. thing1, thing2, etc) then these should really be broken into another table. Listing entities as fields enforces extremely difficult to change business rules within the database. If for some reason you need to add or remove one of these fields in the future, this becomes much more difficult. This isn't a big problem in your case but you never can be 100% certain of your future schema.

On top of this, having entities as fields makes your application developer's life potentially more difficult. In order to find the number of Subjects that a Pair has, that developer would have to check BOTH of these fields every single time. Having a simple SELECT...JOIN statement is much simpler and is not that much more expensive, especially if you have indexed columns correctly.

I would suggest two tables, one for Subject and one for Pair. You can enforce your 2 Subject maximum with a simple function and check constraint like below:

/* Returns the number of Subjects for a given Pair */
CREATE FUNCTION ValidateSubjectCount(@pairID int)
RETURNS int
AS
BEGIN
    RETURN (SELECT COUNT(*) FROM Subject WHERE pairID = @pairID);
END;

/* Assures the Pair table has no more than 2 Subjects */
ALTER TABLE Subject
ADD CONSTRAINT chk_SubjectCount
CHECK (dbo.ValidateSubjectCount(pairID) <= 2);

Upvotes: 1

Related Questions