Best way to model Natural and Legal Customers all together

Question

I want to design my project's data model but I have a problem in "Customer" section since I have two types of customers: Natural people and Legal entities Which is the best way:

Method 1: To have 3 tables like this:

    Customers:
    ID int not null (PK)
    CustomerType bit not null
    NaturalPersonID int (FK)
    LegalPersonID int (FK)

    NaturalPeople:
    ID int not null (PK)
    FirstName nvarchar(50) not null
    LastName nvarchar(50) not null
    NationalSecurityNumber char(10) not null
    ...

    LegalEntities:
    ID int not null (PK)
    CompanyName nvarchar(100) not null
    RegistrationNumber char(20) not null
    ...

which either NaturalPersonID or LegalPersonID is filled and the other is null and CustomerType shows the type of customer (Natural or Legal)

Method 2: To have one table with all fields:

    ID int not null (PK)
    CustomerType bit not null
    FirstName nvarchar(50)
    LastName nvarchar(50)
    NationalSecurityNumber char(10)
    CompanyName nvarchar(100)
    RegistrationNumber char(20)
    ...

Which for each customers some fields are filled and the others are null

Method 3: To have one table with some fields:

    ID int not null (PK)
    CustomerType bit not null
    FirstName nvarchar(50)
    LastName nvarchar(100)
    NationalSecurityNumber varchar(20)
    ...

Which the fields are filled for the natural customers naturally. But if the customer is a Legal one, we put data logically. For example CompanyName in the LastName field and RegistrationCode in the NationalSecurityNumber field and the FirstName field is null.

Method 4: any other way that I didn't think of and you can suggest

P.S. I'm implementing my database in MS SQL Server 2012

Answer 1

Any of the approaches will have pros and cons, any of them would be applicable based on your application requirements and analysis.
BTW I will prefer to use Method 1 with some considerations:

1. Customer table will be the base table for NaturalPeople and LegalEntities, primary key of the Customer will be the primary key of the two others.
   
2. Customer table will contain:
   Shared information of the two others, like customer-number, customer-type ....
   Search key fields like customer-full-name(standard name), so you may set index on them
   

3. Avoid using a field for two different business values, like:

   The fields are filled for the natural customers naturally. 
   But if the customer is a Legal one, we put data logically. For example 
   CompanyName in the LastName field and RegistrationCode in
   the NationalSecurityNumber field and the FirstName field is null.
   

   Soon or late you will suffer if not separating the fields, due to violationg first normal form (think that if National_Security_Number ) is a mandatory value for NaturalPeople and RegistrationCode is an optional value for LegalEntities. You can not set a unique key or mandatory check on the field.

4. Other entities (like accounts, Signs, Address ...) will having reference only to the Customer table.

5. You will need to implement a simple search on Customer table and an advanced search for legal and natural customers.

Answer 2

In this scenario I usually do one table Customer which has a PK and a discriminator column CustomerType, and two detail tables, one for Natural and one for Legal, but primary keys for those additional tables are the same as PK of the Customers table (similar to your method one, but without separate keys for two subtype tables). That way queries are simpler, you can enforce a 1:0 constraint between master and detail, there are no surrogate keys and data is normalized.