Modeling explicit 1 to 2 relationship in database -

If I want to have 1 to 2 relationships, then I am curious, what is the best way to make this model? That will change from 1 to 2. There will be a subject related to a pair, so there is a pair object in which there will always be subject 1 and subject 2. Is this a bad form for the topic1 and topic2 in the pair object? I realized that a join table will work properly, but one topic can be related to more than 1 pair and if I already have a pair object, then it would be easy to break the topic 1, then search for an inserted table Need to do? Or it's just bad data modeling

If you know that there will be only 1 to 2 topics in a pair And you are in fact ... really sure that it will never change, so you can do it like this.

It is being said, this is a bad design for some reasons. Generally, if you have fields of the same unit type (i.e., thing1, thing 2, etc.) then this should actually be broken into another table. As a field, listing entities are extremely difficult to change business rules within the database. If for some reason you need to add or delete any of these fields in the future, it becomes very difficult, this is not a big problem in your case, but you do not have 100% definitely do some schema of the future. You can.

Above this, those who have such fields as fields make your application developer's life potentially difficult to achieve the number of subjects of a pair, to develop that developer in these two areas Check all the time. A simple SELECT ... JOIN statement is very simple and it is not very expensive, especially if you have correctly indexed the column.

I will suggest two tables, one for another pair for the subject. You can apply your maximum of 2 topics with your simple subject and check the barrier like this:

/ * Returns the number of topics for a given pair * / Create function valid authenticator (pairID int) return int like return (SELECT COUNT (*) subject to WHERE pairID = @pairID); End; / * Adds that there are no more than 2 topics in the join table * / add optional table topic; insert chk_SubjectCount check (dbo.ValidateSubjectCount (pairID) & lt; = 2);