Database design

Question

Hello, I've a system, that have two types of users (Companies and individuals).all types have a shared set of properties but they differ in another. What is the best design merge all in one table that allows null for unmatched properties, or separate them in two tables related to a basic table with a one to one relationship. Thanks.

Answer 1

For better performance, Use one table for both, allow nulls for different properties and add a type attribute.

The one-one relationship (equvalent to subclassing in OO world) will make your schema more maintainable and wasier to understand, but involves a performance hit. Choose your pill.

Answer 2

Performance-wise, this is a matter of tradeoff.

Selecting the properties from another table will require an additional JOIN (which is bad for performance) but keeps the main table smaller (which is good for performance).

JOIN is quite a costly operation, so unless you have, like, 200 of these properties (which will increase the table much), you better keep them in one table.

Separating the tables, however, will make your CHECK and NOT NULL constraints more simple.