用于存储不同类型“人物”的SQL数据库设计

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

我需要在关系数据库中使用SQL为具有Name,LastName等列的人创建一个表。

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?

我是否需要为每种不同类型的Person创建一个表,或者是否可以将一个表用于所有三种类型?

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

如果我使用单个表,那么如果一个类型的“Person”(如卖方)具有与另一个Person类型不同的属性,该怎么办?

3 个解决方案

#1

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:

您可以创建一个或两个表。当然,您可以为每个用户角色创建3个表。这取决于你想要达到的目标。您的问题的常见解决方案是:创建两个表,一个用于用户,一个用于他们的角色(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;

#2

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

我将创建一个表Person,其中personId作为主键,将包含所有类型的公共属性。(卖方,买方,客户)

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

然后我将创建PersonTypes,一个小参考表,它将声明不同类型的代码。

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

然后,对于每种类型,我将创建一个单独的表,引用Person表和PersonType表,其中包含所有唯一属性。

#3

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.

卖家可以成为买家还是客户?如果可以的话,将它们全部放在同一个表中,这样就不会有相同数据的单独副本。

#1

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;

#2