我想建立一个“签到”服务，比如FourSquare或Untappd。
如何设计合适的数据库模式来存储签入？
例如，假设我正在开发“芝士饼”，帮助人们跟踪他们尝试过的美味芝士。
可以签入的项目表相当简单，看起来像
+----+---------+---------+-------------+--------+
| ID | Name | Country | Style | Colour |
+----+---------+---------+-------------+--------+
| 1 | Brie | France | Soft | White |
| 2 | Cheddar | UK | Traditional | Yellow |
+----+---------+---------+-------------+--------+
我也会为用户准备一张桌子，比如
+-----+------+---------------+----------------+
| ID | Name | Twitter Token | Facebook Token |
+-----+------+---------------+----------------+
| 345 | Anne | qwerty | poiuyt |
| 678 | Bob | asdfg | mnbvc |
+-----+------+---------------+----------------+
记录用户已签入特定奶酪的最佳方式是什么？
例如，我想记录安妮登记入住的法国奶酪数量。如果瑟曦吃了卡门贝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 |
+-----+------+------+--------+------+------+---------+---------+
那看起来很不体面，很难维持。所以我应该有单独的记录登记表吗？
最佳答案
不，不要放在users表中。这些信息最好存储在连接表中，该表表示用户和cheese之间的多对多关系。
联接表（我们将调用cheeses_users）必须至少有两列（user_ID, cheese_ID），但第三列（时间戳）也很有用。如果将timestamp列默认为CURRENT_TIMESTAMP，则只需将user_ID, cheese_ID插入表中即可登录签入。
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