mysql - 用于任务管理的数据库模式

Question

我必须管理一个待办事项列表，我正在寻找在我的 MySQL 数据库中实现它的最佳方法。

我的待办事项 list 如下

• 术前
  • 任务 1
  • 任务2
  • 任务 3
  • 任务4
• 接收图像和数据
  • 任务 1
  • 任务2
• ...
• ...
• 术后
  • 任务 1
  • 任务 2

我想像这样构建我的模式:

*************
*** steps ***
*************
id_step 
name_step 

1|Pre-op
2|Receipt images and data
3|...
8|Post-op

---

*************
*** tasks ***
*************
id_task 
name_task 

1|Task1
2|Task2
3|...
8|Task56

---

****************
*** todolist ***
****************
id_todolist 
step_id 
task_id
state 

1|1|1|archived
2|1|2|current
3|...
x|8|56|current

我做对了吗？还是有更好的方法进行？

不同的任务是独一无二的。最后，我将至少有两个这样的列表。

我管理外科手术。目前申请了两项专利。所以有两种不同的管理操作的方法。 future ，我们可以想象还会申请其他专利。

Answer 1

也许这有点晚了 :D 但是..

我假设你想要这样的东西:

以下是如何使用一些演示数据创建此设置到空数据库:

CREATE TABLE `todolists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order` (`order`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `todolists` (`id`, `name`, `description`, `order`) VALUES
    (1, 'todolist1name', NULL, 1),
    (2, 'todolist2name', NULL, 2);

CREATE TABLE `steps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text,
  `todolist_id` int(11) NOT NULL,
  `order_in_todolist` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `step_order_in_todolist` (`todolist_id`,`order_in_todolist`),
  CONSTRAINT `FK1: belong to todolist` FOREIGN KEY (`todolist_id`) REFERENCES `todolists` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `steps` (`id`, `name`, `description`, `todolist_id`, `order_in_todolist`) VALUES
    (1, 'step11name', NULL, 1, 1),
    (2, 'step12name', NULL, 1, 2),
    (3, 'step13name', NULL, 1, 3),
    (6, 'step14name', NULL, 1, 4),
    (7, 'step21name', NULL, 2, 1),
    (8, 'step22name', NULL, 2, 2),
    (9, 'step23name', NULL, 2, 3);

CREATE TABLE `states` (
  `name` char(20) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `states` (`name`) VALUES
    ('Archived'),
    ('Current');

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text,
  `state` char(20) NOT NULL,
  `step_id` int(11) NOT NULL,
  `order_in_steplist` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique order in steplist` (`step_id`,`order_in_steplist`),
  KEY `FK1: reference states` (`state`),
  CONSTRAINT `FK1: reference states` FOREIGN KEY (`state`) REFERENCES `states` (`name`),
  CONSTRAINT `FK2: belongs to step` FOREIGN KEY (`step_id`) REFERENCES `steps` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tasks` (`id`, `name`, `description`, `state`, `step_id`, `order_in_steplist`) VALUES
    (1, 'Task111name', NULL, 'Archived', 1, 1),
    (2, 'Task112name', NULL, 'Current', 1, 2),
    (3, 'Task113name', NULL, 'Current', 1, 3),
    (4, 'Task114name', NULL, 'Current', 1, 4),
    (5, 'Task121name', NULL, 'Current', 2, 1),
    (6, 'Task122name', NULL, 'Current', 2, 2),
    (7, 'Task123name', NULL, 'Current', 2, 3),
    (8, 'Task131name', NULL, 'Current', 3, 1),
    (9, 'Task132name', NULL, 'Current', 3, 2),
    (10, 'Task133name', NULL, 'Current', 3, 3),
    (12, 'Task141name', NULL, 'Current', 6, 1),
    (13, 'Task142name', NULL, 'Current', 6, 2),
    (14, 'Task143name', NULL, 'Current', 6, 3),
    (15, 'Task211name', NULL, 'Archived', 7, 1),
    (16, 'Task212name', NULL, 'Archived', 7, 2),
    (17, 'Task213name', NULL, 'Archived', 7, 3),
    (18, 'Task221name', NULL, 'Archived', 8, 1),
    (19, 'Task222name', NULL, 'Archived', 8, 2),
    (20, 'Task223name', NULL, 'Current', 8, 3),
    (21, 'Task231name', NULL, 'Current', 9, 1),
    (22, 'Task232name', NULL, 'Current', 9, 2),
    (23, 'Task233name', NULL, 'Current', 9, 3);

CREATE VIEW `show_all_list_with_steps_and_tasks` AS SELECT `todolists`.`order` AS `todolist_number`, `todolists`.`name` AS `todolist_name`, `steps`.`order_in_todolist` AS `step_order`, `steps`.`name` AS step_name, `tasks`.`order_in_steplist` AS `task_order`, `tasks`.`name` AS `task_name`, `tasks`.`state`
FROM `todolists` JOIN `steps` ON `todolists`.`id` = `steps`.`todolist_id` JOIN `tasks` ON `steps`.`id` = `tasks`.`step_id` 
ORDER BY `todolist_number`, `step_order`, `task_order` ;