清单数据库设计。一种关系和存储用户信息。

I am basically designing a web checklist. The process is as follows: User logs in, selects the "Job Name" for a list, clicks on it, goes to next page, selects "Procedure list" from a list, clicks on it, goes to next page, there he sees a checklist where he can basically add comments, and click check box on individual listings. I know how to code most of it, but at the moment i'm trying to figure out how to setup the relationships + what extra tables to add to hold the information.

我基本上是在设计一个网页清单。流程如下:用户登录时,选择“名称”列表中,点击它,进入下一个页面,从列表中选择“程序列表”,点击它,进入下一个页面,在那里,他看到一个清单,可以添加评论,以及个人列表点击复选框。我知道如何编写大部分的代码，但目前我正在试图弄清楚如何设置关系+添加什么额外的表来保存信息。

General layout I have at the moment:

目前的总体布局:

Table: User_list
User_ID
Username

Table: Job_list
Job_ID
Job Name

Table: Procedure_List
Procedure_ID
Procedure Name
Job_ID

Table: Check_List
Job_ID
Checklist_ID
Description

Job_ID -> Procedure_ID -> Checklist_ID is one to many... but how to add the user list in order to store all the changes done by the user.

Job_ID ->过程-> Checklist_ID是一个对许多…但是如何添加用户列表来存储用户所做的所有更改。

So you can basically have one page where you see:

你可以看到一个页面

Job Name
Procedure
Checklist done

and all the details done by the users.

以及用户所做的所有细节。

1 个解决方案

#1

I'm assuming the relationships are Job 1:m Procedure 1:m Checklist. And a user may choose any number of jobs. The combination of Job/Procedure/Checklist is chosen by the user. For example, I assume a Job may have 10 associated procedures and the user selects 1 or more of these. Same for Procedure: A given procedure has certain checklists associated with it and the user may select any number of these checklists.

我假设这些关系是工作1:m程序1:m检查表。用户可以选择任意数量的工作。工作/程序/清单的组合是由用户选择的。例如，我假设一个作业可能有10个相关的过程，而用户选择了其中的一个或多个。同样的程序:给定的程序有一些与之关联的检查列表，用户可以选择任意数量的这些检查列表。

Use "join tables".

使用“加入表”。

User_Job table. A user may be associated with any job or any number of jobs. The user_ID and job_ID go into the User_Job table. Make the primary key the User_ID + job_ID.
User_Job表。用户可能与任何工作或任何数量的工作相关。user_ID和job_ID进入User_Job表。使主键为User_ID + job_ID。
Job_Procedure table. Put the User_Job key (both columns) and procedure_id in this table. Make the primary key User_ID, Job_ID, Procedure_ID
Job_Procedure表。将User_Job键(两列)和进程id放在该表中。使主键User_ID、Job_ID、进程返回id。
Procedure_Checklist table. Put the Job_procedure key (all columns) & the checklist_id in this table. Make the primary key a compound using all the columns.
Procedure_Checklist表。将Job_procedure键(所有列)和checklist_id放在该表中。使用所有列使主键成为一个化合物。

Primary Keys thoughts

主键的想法

A sequence number for a primary key for each table will limit the number of columns in the related tables. However this key has no real meaning and if you're looking at the Procedure_Checklist table, for example, you cannot tell which job & user without querying the other tables - PITA. It's also meaningless to sort such a key. And it does not prevent duplicate rows.

每个表的主键的序列号将限制相关表中的列数。但是，这个键并没有真正的含义，如果您查看的是processdure_checklist表，例如，您无法判断哪个作业和用户没有查询其他表——PITA。对这样的键排序也是没有意义的。而且它不会阻止重复的行。

#1