We are moving to MSSQL. I have all the table structures entered and primary keys setup on all tables. I read about setting constraints, but the majority of my relationships will be one to many. I do not want to setup a unique constraint on a FK since it will be duplicated in the table, but it relates to a PK in another table.
EE_ID INT IDENTITY,
EE_FNAME NVARCHAR (40),
EE_LNAME NVARCHAR (40)
EEREC_ID INT IDENTITY,
In the above example, EE_ID is the PK for EMPLOYEES, EEREC_ID is the PK for EE_RECORDS, EE_ID is a FK for EE_RECORDS. There can be many EE_RECORDS for one EMPLOYEES. How should I setup the relationships? What are the benefits of having these relationships? What are the recommended practices for setting up other types of relationships?