Database design, customers (companies / individuals) in the same table?

Found possible duplicate : Database design, multiple types of customers in the same table

Hi everyone.

I’m actually looking for a nice way to create a database with 2 kinds of customers : Individuals and Companies. This is for a website and I’m using CodeIgniter framework (not sure it’s important to precise).

I will only write the “important” fields to make sure it’s easier to understand what I’m trying to do.

• Both kind of customers have these required data : email (it’s the login), password
• For companies : siret (SIRET number), name
• For individuals : first_name, last_name

So, I’m wondering if it’s better to get everything in a single table (with NULL fields depending of the customer type) or to get splited between 2 tables (company, individual) with a “master” table (account).

---

My idea of a single table

+----------------+--------------------+
|    account     |                    |
+----------------+--------------------+
| id (PK)        |                    |
| email (UNIQUE) |                    |
| password       |                    |
| siret (UNIQUE) | NULL if individual |
| company_name   | NULL if individual |
| first_name     | NULL if company    |
| last_name      | NULL if company    |
| is_company     | necessary ?        |
+----------------+--------------------+

If first_name and last_name are null, it is a individual.
If siret and company_name are null, it is a company.
Or maybe directly the use of a is_company field ?

---

My idea of multiple tables

+----------------+--------------------+--------------------+
|    account     |       company      |     individual     |
+----------------+--------------------+--------------------+
| id (PK)        | account_id (PK/FK) | account_id (PK/FK) |
| email (UNIQUE) | name               | first_name         |
| password       | siret (UNIQUE)     | last_name          |
| is_company ?   |                    |                    |
+----------------+--------------------+--------------------+

Here, when creating a new account, it will populate 2 tables : account and either company or individual regarding of the type of customer.
I guess we need to run 2 queries in this case to get every data for a specific customer.

I don’t know what’s the best approach, nor if there is a kind of “often used standard” for this. But what I am sure about is that I would be grateful to get your opinions.

Answer

Read up on Table Inheritance. Single Table Inheritance is nice and simple.

create table parties (
  party_id int primary key,
  type text not null, -- use a check constraint or foreign key in real life
  individual_given_name text null,
  individual_surname text null,
  organization_legal_name text null,
  organization_siret text null
);

In the above, use check constraints to ensure integrity depending on party type.

Other types of parties could be Company Departments, or Government Agencies, or Groups of Individuals (like a family).

“Accounts” isn’t a great term to use as it has several meanings in computing. Users is probably better.

create table users (
  user_id int primary key,
  email_address text not null unique,
  password_hash text not null,
  party_id int not null references parties(party_id)
);