SQL Server Nullable Foreign Key Normalization Question

Posted on 2014-07-15
Last Modified: 2014-07-15
Hello all,

I have an application that has a table currently Customer that has a non nullable field LocationId.  It is a 1 to 1 relationship currently.   We have determined that we have to decouple the LocationId as the Customer may not have a location.   Right now the LocationId is a FK in the Customer table to a PK table called Location.

The question is this should we just make the LocationId a nullable field in the Customer table.   This causes least impact on changes we need to make as we use Entity Framework in our app and it's everywhere.    The other option is making a table called CustomerLocation that has the two PK fields CustomerId and LocationId and deleting it from the Customer table.   This really looks to be a many to many although I can put a constraint on it.   This though we have to in our app go after the FirstOrDefault record everywhere.  If it is best to do it this way then we will just have to bite the bullet.

Any suggestions what is the best practice here?   Having a nullable FK not sure is best but impact is higher on the app code.

Thanks all
Question by:sbornstein2
    LVL 34

    Assisted Solution

    by:Brian Crowe
    There is nothing wrong with having a nullable foreign key field and that is the route I would suggest.
    LVL 68

    Accepted Solution

    If it is (still) a 1-1, and you expect that it will remain a 1-1 even though you've decoupled them, then I would use a nullable foreign key.  There's absolutely no design issue with that, if the data is truly a zero or one occurrence, never more than one.
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    If on the other hand two or more customers can have the same location then your second option would make more sense.

    Author Comment

    awesome thanks all.   Appreciate the feedback its going to be a 0 to 1 or 1 to 1.

    Author Closing Comment

    thanks all.  giving the top points to Scott good explanation I can use to back myself up as well 0 to 1 or 1 to 1.  Thanks all for the feedback.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now