数据库模式，一个表或两个表

My application will allow users to have a contact list. This is my current schema:

我的应用程序将允许用户拥有联系人列表。这是我目前的模式:

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `contact_request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_address` varchar(50) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_address` (`email_address`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;

When a user tries to add another user as a contact, a record is created in the contact_request table. If the user receiving the request rejects the request, the contact_request record is deleted. If the user decides to accept the request, the data from the contact_request table is added to the contact table then deleted from the contact_request table.

当用户试图将另一个用户添加为联系人时，将在contact_request表中创建一条记录。如果接收请求的用户拒绝请求，则删除contact_request记录。如果用户决定接受请求，则将contact_request表中的数据添加到contact表中，然后从contact_request表中删除。

I realized that I could do this in another way where I drop the contact_request table and add another field to the contact table e.g: status that signifies whether a contact was just requested or if it is an accepted request.

我意识到我可以用另一种方式来做，即删除contact_request表并向contact表e添加另一个字段。g:表示联系人是否被请求的状态，或者是否被接受的请求。

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `status` tinyint(1) not null,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

The advantage I see is that I would have 1 less table. I currently do not see a problem occurring as a result of this change. Is it worth changing this? Are there any other advantages to either method that I might not be aware of. Which is recommended?

我看到的优点是少了一个表。我目前没有看到由于这种变化而产生的问题。这值得改变吗?这两种方法还有其他我可能不知道的优点吗?推荐的是哪一个?

3 个解决方案

#1

One other advantage might be to have this status (either as INT or as CHAR), record requests (Q), accepted contacts (C), rejected requests (J), rejected and re-requested (R), blacklisted (B) and possibly other statuses so you could more easily apply more complicated logics, like "a user cannot request a contact again when it has been rejected twice", etc.

另一个优势是有这个状态(整数或字符),记录请求(Q),接受联系人(C)、(J)拒绝请求,拒绝再次请求(R),列入黑名单(B)和可能的其他状态所以你可以更容易地使用更复杂的逻辑,就像“一个用户不能请求再次联系的时候被拒绝了两次”,等等。

#2

It is worth changing this for more than one reason; as you stated, it will allow you to have one less table. More importantly however, it will allow you to avoid people from requesting contact with someone they've already added without having to query an extra table.

改变这一点值得不止一个原因;正如您所说，它将允许您减少一个表。然而，更重要的是，它将允许您避免人们请求与他们已经添加的人进行联系，而不必查询额外的表。

#3

It would be cleaner in a sense to keep them as two tables. You could purge and keep the queue table small while not having to keep filtering out the not-real-contacts. It sounds like you will never really need to view contacts and requests within the same table, so there is no reason to mash them together just for the sake of it.

从某种意义上说，将它们作为两个表保存是比较干净的。您可以清除并保持队列表较小，同时不必一直过滤不存在的联系人。听起来似乎您永远都不需要在同一个表中查看联系人和请求，因此没有理由仅仅为了这个目的而将它们合并在一起。

On the other hand, the only plus that I can see is that you, umm, have one less table in the db? And a very vague one of not being able to accidentally have a contact exist both in the contact table proper and the request table at the same time (timing bug or something else).

另一方面，我能看到的唯一的好处是你，嗯，在db中有一个更少的表?还有一个非常模糊的问题，即不能在正确的联系人表和请求表同时存在(定时错误或其他问题)。