Search code examples
sqldatabasems-accessrelationships

Checklist database design. One to many relationship and storing user information

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.

So you can basically have one page where you see:

Job Name
Procedure
Checklist done

and all the details done by the users.

Solution

  • 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.

    Use "join tables".

    1. 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.
    2. 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
    3. 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.

    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.