mysql - 如何设计我的数据库以容纳这些数据

Question

我正在为薪资应用程序开发一个数据库，我需要的功能之一是一个表，用于存储一周中每一天在每家商店工作的员工列表。

每个员工都有一个 ID，所以我的表是这样的:

        |   Mon   |   Tue   |   Wed   |   Thu   |   Fri   |   Sat   |   Sun
Store 1 | 3,4,5   | 3,4,5   | 3,4,5   | 4,5,7   | 4,5,7   | 4,5,6,7 | 4,5,6,7
Store 2 | 1,8,9   | 1,8,9   | 1,8,9   | 1,8,9   | 1,8,9   | 1,8,9   | 1,8,9
Store 3 | 10,12   | 10,12   | 10,12   | 10,12   | 10,12   | 10,12   | 10,12
Store 4 | 15      | 15      | 15      | 16      | 16      | 16      | 16
Store 5 | 6,11,13 | 6,11,13 | 6,11,13 | 14,18,19| 14,18,19| 14,18,19| 14,18,19

我的问题是，我如何在我的数据库中表示它？我提出了以下想法:

想法 1:几乎复制上面的设计，创建一个包含以下列的表:[Store_id |星期一 |星期二... |星期六 | Sun]，然后将每天的员工 ID 列表存储为字符串，ID 以逗号分隔。我知道逗号分隔的列表不是好的数据库设计，但有时它们看起来确实很诱人，就像本例一样。

   Store_id |   Mon   |   Tue   |   Wed   |   Thu   |   Fri   |   Sat   
   ---------+---------+---------+---------+---------+---------+---------
        1   | '3,4,5' | '3,4,5' | '3,4,5' | '4,5,7' | '4,5,7' | '4,5,6,7' 
        2   | '1,8,9' | '1,8,9' | '1,8,9 '| '1,8,9' | '1,8,9' | '1,8,9'   

想法 2:创建一个包含以下列的表:[Store_id |天|员工ID]。这样，在特定日期在特定商店工作的每个员工都将成为该表中的一个条目。我看到的问题是这个表会增长得非常快，并且很难在数据库级别可视化数据。

Store_id | Day | Employee_id
---------+-----+-------------
   1     | mon |     3
   1     | mon |     4
   1     | mon |     5
   1     | tue |     3
   1     | tue |     4

这些想法听起来可行吗？有更好的数据存储方式吗？

Answer 1

如果我是你，我会存储员工数据并将数据存储在单独的表中......但仍保留主表的设计。所以做这样的事情

CREATE TABLE stores (
    id INT, -- make it the primary key auto increment.. etc
    store_name VARCHAR(255)
    -- any other data for your store here.
);

CREATE TABLE schedule (
    id INT, -- make it the primary key auto increment.. etc
    store_id INT, -- FK to the stores table id
    day VARCHAR(20),
    emp_id INT -- FK to the employees table id
);

CREATE TABLE employees
    id INT, -- make it the primary key auto increment.. etc
    employee_name VARCHAR(255)
    -- whatever other employee data you need to store.
);

我会为商店和员工准备一张表，这样您就可以拥有每个商店或员工的特定数据

奖励:

如果你想要一个查询来显示商店名称、员工姓名和他们的日程安排等等，那么你所要做的就是连接两个表

SELECT s.store_name, sh.day, e.employee_name
FROM schedule sh
JOIN stores s ON s.id = sh.store_id
JOIN employees e ON e.id = sh.emp_id

但是此查询有局限性，因为您不能按天排序，因此您可以按随机日期获取数据。所以实际上您还需要一个包含当天特定数据的天数表，这样您就可以按开头或周末。

如果你确实想制作一个天数表，那将是同样的事情

CREATE TABLE days(
    id INT,
    day_name VARCHAR(20),
    day_type VARCHAR(55)
    -- any more data you want here
)

其中日期名称为 Mon Tue... day_type 为 Weekday 或 Weekend

然后您需要为您的查询做的就是

SELECT s.store_name, sh.day, e.employee_name
FROM schedule sh
JOIN stores s ON s.id = sh.store_id
JOIN employees e ON e.id = sh.emp_id
JOIN days d ON d.id = sh.day_id
ORDER BY d.id

请注意，日程安排表中 day 的两列将替换为链接到 days 表的 day_id 的一列。

希望对您有所帮助!