简单的客人名单数据库设计(Simple guest list database design)

简单的客人名单数据库设计(Simple guest list database design)

DB设计师的一个问题：

在一个聚会的客人名单中，我们将有主持人（组织和参加聚会的人）和客人（刚刚参加聚会的人）。

有两种类型的客人：

邀请卡的客人：在家中接受邀请卡的人，以及

没有邀请卡的客人：需要携带持有邀请卡的客人陪同的人才能参加派对。

据了解，有必要注册第一类客人的地址，因为有必要知道放弃邀请卡的位置。 此外，对于每个访客，都需要知道邀请他们的主机或访客ID。

问题是： 我应该创建多少个表？ 一个人，其中包括所有观众？ 两个表：一个用于主机，一个用于访客？ 三个表：一个用于主持人，一个用于有邀请卡的客人，另一个用于没有邀请卡的客人？

我在第三个解决方案（三个表）中看到的优势是，我避免在没有邀请卡的情况下为客人留下空白“地址”，并且我可以使用邀请卡注册客人的ID他们。

我很高兴看到你的意见和想法。

A question for DB designers:

In a guest list for a party, we will have Hosts (those who organize and attend the party) and Guests (those who are just attending the party).

There are two types of Guests:

Guest with invitation card: those receiving the invitation card physically at their homes, and

Guests without invitation card: those who need to be accompanied with a Guest that holds an invitation card, in order to enter the party.

It's understood that it's necessary to register the addresses of the first type of Guests, as it's necessary to know where to drop the invitation card. Also, for every Guest, it's required to know the Host or Guest ID from who is inviting them.

The question is: How many tables should I create? A single one, with all the audience in it? Two tables: one for Hosts, one for Guests? Three tables: one for Hosts, one for Guests with invitation card, and another one for Guests without invitation card?

The advantage I see in the third solution (three tables) is that I avoid leaving blank the field "address" for the Guests without invitation card, and I'm able to register the ID of the Guest with invitation card that is going to take them.

I'd be glad to read the opinions and ideas from you.

最满意答案

至少有五张桌子。

Host ---- Host ID Host Name ... Invited Guest ------------- Invited Guest ID Invited Guest Name Invited Guest Address ... Guest ----- Guest ID Guest Name ... Party ----- Party ID Host ID Party Time stamp Party Address ... Party Guest ----------- Party Guest ID Party ID Invited Guest ID Guest ID ...

我总是将表的主（聚类）键定义为自动递增整数或长整数。

外键应该是明显的名称。 Party Guest表中的Guest ID外键是可以为空的外键。 受邀嘉宾可以邀请客人，但不必。

另外两个表将有助于最大限度地减少上述五个表中的重复数量。

Name ---- Name ID Name Address ------- Address ID Address

由于一方的访客可能是另一方的主持人，因此这些表最小化了数据重复。

您只需使用名称ID替换每个名称列，并使用地址ID替换每个地址列。

Five tables, at least.

Host ---- Host ID Host Name ... Invited Guest ------------- Invited Guest ID Invited Guest Name Invited Guest Address ... Guest ----- Guest ID Guest Name ... Party ----- Party ID Host ID Party Time stamp Party Address ... Party Guest ----------- Party Guest ID Party ID Invited Guest ID Guest ID ...

I always define the primary (clustering) key of the table as an auto incrementing integer or long.

The foreign keys should be obvious by the names. The Guest ID foreign key in the Party Guest table is a nullable foreign key. An invited guest can invite a guest, but doesn't have to.

Two more tables would help minimize the amount of duplication in the five tables above.

Name ---- Name ID Name Address ------- Address ID Address

Since guests for one party are likely to be hosts for another party, these tables minimize the data duplication.

You just replace every name column with the Name ID, and every address column with the Address ID.