[ACCEPTED]-Designing 1:1 and 1:m relationships in SQL Server-data-modeling

Any relationship requires that the "parent" table 58 (the one side) have a Primary (or unique) Key 57 (PK), that uniquely identifies each row, and 56 the "child" table (the other side) have 55 a Foreign Key column or columns, that must 54 be populated with values that are the same 53 as some existing value[s] of the Primary 52 Key in the parent table. If you want a 51 one to many (1-M) relationship then the 50 Foreign Key should be an ordinary attribute 49 (column or columns) in the child table that 48 can repeat (there can be many rows with 47 the same value)

If you want a one to one 46 (1-1) relationship then the Foreign key 45 should itself be a Primary Key or unique 44 index in the child table that guarantees 43 that there may be at most one row in the 42 child table with that value.

A 1-1 relationship 41 effectively partitions the attributes (columns) in 40 a table into two tables. This is called 39 vertical segmentation. This is often done 38 for sub-classing the table entities, or, for another 37 reason, if the usage patterns on the columns 36 in the table indicate that a few of the 35 columns need to be accessed significantly 34 more often than the rest of the columns. (Say 33 one or two columns will be accessed 1000s 32 of times per second and the other 40 columns 31 will be accessed only once a month). Partitioning 30 the table in this way in effect will optimize 29 the storage pattern for those two different 28 queries.

Sub-Classing. The above actually creates 27 a 1 to zero or one relationship, which is 26 used for what is called a sub-class or subtype 25 relationship. This occurs when you have 24 two different entities that share a great 23 number of attributes, but one of the entities 22 has additional attributes that the other 21 does not need. A good example might be 20 Employees, and SalariedEmployees. The Employee table would have all the attributes 19 that all employees share, and the SalariedEmployee table 18 would exist in a (1-0/1) relationship with 17 Employees, with the additional attributes 16 (Salary, AnnualVacation, etc.) that only Salaried employees 15 need.

If you really want a 1-1 relationship, then 14 you have to add another mechanism to guarantee 13 that the child table will always have one 12 record for each record/row in the parent 11 table. Generally the only way to do this 10 is by enforcing this in the code used to 9 insert data (either in a trigger, stored 8 procedure or code outside the database). This 7 is because if you added referential integrity 6 constraints on two tables that require that 5 rows always be in both, it would not be 4 possible to add a row to either one without 3 violating one of the constraints, and you 2 can't add a row to both tables at the same 1 time.

One-to-One Relationship

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null [Primary Key, Unique]
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( ForeignKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this case, I can never have more than 10 one row in the ChildTable for a given ParentTable 9 primary key value. Note that even in a One-to-One 8 relationship, one of the tables is the "parent" table. What 7 differentiates a One-to-One relationship 6 from a One-to-Many relationship purely in 5 terms of implementation is whether the ChildTable's 4 foreign key value has a Unique or Primary 3 Key constraint.

One-to-Many Relationship

Create Table ParentTable
    (
    PrimaryKeyCol ... not null Primary Key
    , ...
    )

Create Table ChildTable
    (
    , ForeignKeyCol ... [not] null 
    , ...
    , Constraint FK_ChildTable_ParentTable
        Foreign Key ( PrimaryKeyCol )
        References ParentTable( PrimaryKeyCol )
    )

In this scenario, I can have 2 multiple rows in the ChildTable for a given 1 ParentTable primary key value.

A 1:1 relationship exists where table A 15 and table B only exist once in regards to 14 each other. Example: A student has 1 master 13 student record. The student would be table 12 A and the record in table B. Table B would 11 contain a foreign key to the student record 10 in table A (and possibly vice-versa)

A 1:m 9 relationship exists where table A can be 8 referenced or linked to by many entries 7 in table B. Example: A student can take 6 several books out from the library. The 5 student again would be table A and the book 4 could be the entry in table B. The entry 3 in table B would contain a foreign key to 2 who checked the book out, and many books 1 could reference the same student.