CODEWITHSUNDEEP
sqlattributes
petajamaja
petajamaja

Reputation: 530

SQL attributes depending on type

Let's say I have an entity CLIENT, which can be either PERSON or ORGANIZATION. Depending on which type it is, I have to choose attributes (address, name for organization, date_of_birth,first_name,last_name for person). I have created all three entities, but how can I make the attributes type-dependent?

Seen Database design: objects with different attributes, didn't help...

Upvotes: 4

Views: 398

Answers (2)

Udo Klein
Udo Klein

Reputation: 6902

Either you use 3 tables or you use 1 table and leave the not needed columns null. Which design is superior depends on the use case. Using only 1 table gives simpler queries but requires to change the table for each new subclass. Using multiple tables allows to add more types easily but gives more complicated queries. In doubt I would start with only 1 table but your mileage may vary.

Upvotes: 2

Andomar
Andomar

Reputation: 238176

One typical choice is a 1:1 extension table:

create table client (id int primary key);
create table person (id int foreign key references client(id), ...columns...);
create table organization (id int foreign key references client(id), ...columns...);

However, my preferred choice is to include all columns in the client table. You can have a column for type that is either person or organization. Columns that are not relevant for the row's type can be null. Your queries will be much simpler that way.

Upvotes: 3

Related Questions