SQL Server Create Table with Foreign Key

Question

I want to validate the proper handling of Foreign keys in table. Here are my two tables being created below. It is possible that a person may not have an address listed so I want it to be null. Otherwise I would like to reference a primary key from the address table and store it in the Person table as a foreign key. It is also possible that we may have an address object without a person.

Table for a Person:

CREATE TABLE Person
(
    PersonID int IDENTITY PRIMARY KEY,
    FName varchar(50) NULL,
    MI char(1) NULL,
    LName varchar(50) NULL,
    AddressID int FOREIGN KEY REFERENCES Address(AddressID) NULL,
)

Table for Address:

CREATE TABLE Address
(
    AddressID int IDENTITY PRIMARY KEY,
    Street varchar(60) NULL,
    City varchar(50) NULL,
    State varchar(2) NULL,
    Zip varchar(10)NULL,
    Intersection1 varchar(60) NULL,
    Intersection2 varchar(60) NULL,
)

Also Q2 I have never worked with triggers but I am assuming the way to handle an insert would be to use a stored procedure to insert the address first, get the primary key, then pass it to a stored procedure to insert into Person table?

Answer 1

Question for you: is it possible that more than one person lives on the same address? Also, is it possible that one person lives on more than one address?

If this is the case consider M:N relationship with additional PersonAddress table.

Otherwise if this is not the case I’d ask myself “is it more likely that you’ll have person without an address or address without the person?” Goal is to determine if you should store AddressID with Person table or PersonID with Address table?

Answer 2

I would change Address like this:

 CREATE TABLE Address
 (
     AddressID int IDENTITY,
     Street varchar(60) NULL,
     City varchar(50) NULL,
     State varchar(2) NULL,
     Zip varchar(10)NULL,
     Intersection1 varchar(60) NULL,
     Intersection2 varchar(60) NULL,
 )
 Alter Table Address Add Constraint
 PK_Addresses Primary Key Clustered  
 (City Asc, Zip Asc, Street asc)

 Create Unique NonClustered Index IX_Address_Key
 On Address{AddressId)

I would do this because, then, when you add a person with a specified StreetAddress, City and Zip, You can do this in a SavePerson Stored proc.

If Exists (Select * From Address
           Where Street = @Street
              And City = @city
              And Zip = @zip)
   Begin
       Select @AddressId = AddressId
       From Address
       Where Street = @Street
           And City = @city
           And Zip = @zip
   End
Else Begin
      Insert Address(Street, City, State, Zip)
      Values(@street, @city, @state, @zip)
      Set @AddressId = Scope_Identity()
   End

And then use the value of the t-sql variable @AddressId in your Insert into the Person table. You would still use the AddressId for joins in other SQL statements, but you have a Primary Key on City, Zip and Street address that will prevent you from inserting duplicate addresses in the Address table. Make this the clustered index in case you may need to retrieve or process groups of addresses that are all in one zip, or all in one city...