CODEWITHSUNDEEP
sqldatabase-design
samy
samy

Reputation: 67

SQL database design for storing different types of "Person"

I need to create a table in a relational database using SQL for persons having the columns Name, LastName and so on.

I'll have three different kinds of People: Seller, Buyer and Customer.

Every person has other information/attributes.

Do I need to create a table for each different type of Person or can a single table be used for all three types?

If I used a single table, what if one type of "Person", say Seller, has different attributes from another Person type?

Upvotes: 1

Views: 3272

Answers (3)

Gregory Nozik
Gregory Nozik

Reputation: 3374

I would create one table Person , with personId as primary key that will contain common properties for all types.(Seller , Buyer , Customer)

Then I would create PersonTypes, a small reference table , that will declare codes for the different types .

Then for each type I would create a separate table with reference to Person table and PersonType table that includes all the unique properties.

Upvotes: 4

Ernest Staszuk
Ernest Staszuk

Reputation: 214

You can create 1 or two tables. Of course you can create 3 tables for each user role. It depend's on what you would like to achieve. Common solution to your question is: create two tables, one for users and one for their roles (examples for mysql:

Create table `person_role` (
id int not null,
roleName varchar(255) not null,
PRIMARY KEY(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Create table `person`(
id int not null.
name varchar(255) not null,
lastName varchar(255) not null,
role int not null,
PRIMARY KEY(`id`),
CONSTRAINT FOREIGN KEY(`role`) REFERENCES person_role(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Upvotes: 1

jpa
jpa

Reputation: 12176

Can a seller ever be a buyer or a customer? If they can, put it all in the same table so that you don't have separate copies of the same data.

Upvotes: 0

Related Questions