Good db table design: One table mixing different entities or separate table for each entity

Question

What is better database design?

Having one big table that could contain different "types" of records eg: employees, cars, cell-phones, and in order to identify each type of record, we have a column called type.

So the table would have columns that look like

id | type | name

1 | car | Ford

2 | car | Toyota

3 | phone | motorola

4 | employee | Jack

5 | employee | Aneesh

6 | phone | Nokia

7 | phone | Motorola

or have different tables for each type

eg:

Employees

id | name

Cars

id | name

Phones

id | name

These tables could have foreign key references from other tables. Now if each table had different columns the decision would have been simple that you can't have that in the same table. So option 1 is probably ruled out (unless all columns that are not common are nullable). But what if these different entities had similar columns, in that case what is better design?

What might be the arguments for and against each?

Answer 1

I agree with everyone - definitely use separate tables. You loose nothing by having separate tables - just because you have a few more tables, your database won't get any slower or less manageable.

But you gain a lot - you don't have to have lots of fields that make no sense for one type of entity, and so on. You adhere to 2NF as many have pointed out, and that's definitely a good thing!

Check out this interesting article on Simple Talk called

Five Simple Database Design Errors and how to avoid them

Error #1 is what the author calls the "common lookup table", which sounds a lot like what you're trying to do - but for real live data.

Read the article, internalize all its requirements - excellent stuff and highly recommended!

Answer 2

Since they are really different types, I'd suggest storing thedm in separate tables. This prevents having to maintain a list of types, since they're all in their own tables. Furthermore, if, in the future one of these types is extended (e.g. you're going to store phone numbers for employees), you're not getting any weird relationships like phone numbers for cars. It also makes your database easier to understand and maintain.