mysql - 如何为 "Check In"社会服务设计数据库

Question

我想构建一个像 FourSquare 这样的“签到”服务或 Untappd .

如何设计合适的数据库架构来存储签到？

例如，假设我正在开发“CheeseSquare”来帮助人们跟踪他们尝试过的美味奶酪。

可以 checkin 的项目表非常简单，看起来像

+----+---------+---------+-------------+--------+
| ID |  Name   | Country |    Style    | Colour |
+----+---------+---------+-------------+--------+
|  1 | Brie    | France  | Soft        | White  |
|  2 | Cheddar | UK      | Traditional | Yellow |
+----+---------+---------+-------------+--------+

我也会为用户准备一张 table ，比如说

+-----+------+---------------+----------------+
| ID  | Name | Twitter Token | Facebook Token |
+-----+------+---------------+----------------+
| 345 | Anne | qwerty        | poiuyt         |
| 678 | Bob  | asdfg         | mnbvc          |
+-----+------+---------------+----------------+

记录用户签到特定奶酪的最佳方式是什么？

比如我想记录安妮打卡了多少法国奶酪。 Bob 检查过哪些奶酪等。Cersei 是否吃过超过 5 次卡门培尔奶酪等。

我是否最好将这些信息放在用户表中？例如

+-----+------+------+--------+------+------+---------+---------+
| ID  | Name | Blue | Yellow | Soft | Brie | Cheddar | Stilton |
+-----+------+------+--------+------+------+---------+---------+
| 345 | Anne |    1 |      0 |    2 |    1 |       0 |       5 |
| 678 | Bob  |    3 |      1 |    1 |    1 |       1 |       2 |
+-----+------+------+--------+------+------+---------+---------+

这看起来相当笨拙且难以维护。那么我应该有单独的表来记录记录吗？

Answer 1

不，不要将它放入users 表中。该信息最好存储在表示用户和奶酪之间的多对多关系的连接表中。

连接表(我们称之为 cheeses_users)必须至少有两列(user_ID, cheese_ID)，但第三列(时间戳)也很有用.如果您将时间戳列默认为 CURRENT_TIMESTAMP，则只需将 user_ID, cheese_ID 插入表中即可记录 checkin 。

cheeses (ID) ⇒ (cheese_ID) cheeses_users (user_ID) ⇐ users (ID)

创建为:

CREATE TABLE cheeses_users
  cheese_ID INT NOT NULL,
  user_ID INT NOT NULL,
  -- timestamp defaults to current time
  checkin_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- (add any other column *specific to* this checkin (user+cheese+time))
  --The primary key is the combination of all 3
  -- It becomes impossible for the same user to log the same cheese
  -- at the same second in time...
  PRIMARY KEY (cheese_ID, user_ID, checkin_time),
  -- FOREIGN KEYs to your other tables
  FOREIGN KEY (cheese_ID) REFERENCES cheeses (ID),
  FOREIGN KEY (user_ID) REFERENCES users (ID),
) ENGINE=InnoDB; -- InnoDB is necessary for the FK's to be honored and useful

要记录 Bob & Cheddar 的签到，请插入:

INSERT INTO cheeses_users (cheese_ID, user_ID) VALUES (2, 678);

要查询它们，您可以通过此表加入。例如，要查看每个用户每种奶酪的数量，您可以使用:

SELECT
  u.Name AS username,
  c.Name AS cheesename,
  COUNT(*) AS num_checkins
FROM
  users u
  JOIN cheeses_users cu ON u.ID = cu.user_ID
  JOIN cheeses c ON cu.cheese_ID = c.ID
GROUP BY
  u.Name,
  c.Name

要获取给定用户最近 5 次签到，例如:

SELECT
  c.Name AS cheesename,
  cu.checkin_time
FROM
  cheeses_users cu
  JOIN cheeses c ON cu.cheese_ID = c.ID
WHERE 
  -- Limit to Anne's checkins...
  cu.user_ID = 345
ORDER BY checkin_time DESC
LIMIT 5