Working on a database design for computer repair class

[chrscote]

I am trying to create a database for a computer repair class. I have gotten the database designed for when a single student works on a computer issue to the end. However, there are cases where 2 or 3 students can team up on a single issue or where one student can pass the issue on to another student. These cases are befuddling me in the database design. Below, I have my current database design. Can someone please help me determine what I need to do to accommodate the times when multiple people work on the same computer issue?

[NTC]

Delete the field 'CustomerID' from the Issues table (and of course its relationship). but that's not really your question ....

For multiple techs working on 1 computer - you implement that by placing a sub form for Techs inside the main form for Issues
or perhaps you implement it up at the computer main form and have a subform for multiple issues - - or potentially both.....

right now you have techs linked to an issue, and then the issue linked to the PC - - - which is ok if you are that structured.... you could link techs to the PC and issues also link to the PC and then they are both there but less structured....

but one only gets so far in table relationships - it is the implementation at the form level with the sub forms that gives you the displays that you need.....

[chrscote]

I understand removing the CustomerID field. I can easily get the customer's name using joins between the computer and customer tables. However, my main issue is when 2 technicians work on the same issue, either together (as a team) or separately (where one tech passes it off to another). What's the best way to store these cases in the database? Should I add another field to the Assignments table or perhaps use a string value for TechID in that table where I use a comma-separated list of techIDs who are working on the issue at a single time.


Chris

[ssanfu]

You will probably have problems or major headaches because every table except "Status" has the same name for the primary key field.
Better names would be to add the table name to "ID"- examplles: "IssueID", "CustomerID", "ComputerID",...

I use "CustomerID_PK" and "CustomerID_FK". Much easier to determine the PK and FK fields.

cases where 2 or 3 students can team up on a single issue

Consider a junction table between "Techs" and "Assignments"....
Table "Assign_Techs"
Fields:

Assign_Tech_PK	Autonumber
AssignmentID_FK	Long
TechID_FK	Long

[NTC]

notice the advice on field naming...

but your reply involves simply the issue of 1:Many - and the answer depends on your business do you want 'many' techs related to the 'issue' or related to the 'PC' ? I would think just the PC but....you have that table Assignments - and not sure that is really needed....

so I would say that Customer:Computers is 1:Many and then Computers:Issues is 1:Many and Computers:Techs is 1:Many - - - but you could do Issues:Techs instead for a more precise alignment of the tech to the specific issue... if Issues get broken into sub Issues then the Assignments table could make sense.....

[chrscote]

You will probably have problems or major headaches because every table except "Status" has the same name for the primary key field.
Better names would be to add the table name to "ID"- examplles: "IssueID", "CustomerID", "ComputerID",...

I use "CustomerID_PK" and "CustomerID_FK". Much easier to determine the PK and FK fields.


Consider a junction table between "Techs" and "Assignments"....
Table "Assign_Techs"
Fields:

Assign_Tech_PK	Autonumber
AssignmentID_FK	Long
TechID_FK	Long

I had been doing the naming in the queries using "AS", but I can see how this would be more helpful. I'm just confused about the junction table and how that would help with the multiple techs per issue/computer.

Chris

[chrscote]

notice the advice on field naming...

but your reply involves simply the issue of 1:Many - and the answer depends on your business do you want 'many' techs related to the 'issue' or related to the 'PC' ? I would think just the PC but....you have that table Assignments - and not sure that is really needed....

so I would say that Customer:Computers is 1:Many and then Computers:Issues is 1:Many and Computers:Techs is 1:Many - - - but you could do Issues:Techs instead for a more precise alignment of the tech to the specific issue... if Issues get broken into sub Issues then the Assignments table could make sense.....

I really want the 'many' techs related to the issue simply because the customer could bring the computer in while a different class is in session.

Chris

[ssanfu]

Yep, I misunderstood ... But isn't the table "Assignments" the junction table between "Issues" and "Techs"?


I would consider moving the field "ActionsTaken" from "Assignments" to a new table. Maybe also have fields for "Tech" and "ActionDate" in the new table.

[chrscote]

Yep, I misunderstood ... But isn't the table "Assignments" the junction table between "Issues" and "Techs"?


I would consider moving the field "ActionsTaken" from "Assignments" to a new table. Maybe also have fields for "Tech" and "ActionDate" in the new table.

In answer to your question, yes, the Assignments table is a junction between Issues and Techs.
I had forgotten the requirement of allowing for multiple techs working on the same issue either as a team or separately. I think your suggestion for moving the ActionsTaken and Tech fields and adding an ActionDate in a new table makes sense. Am I correct that this would then move the Tech's ID relationship to this new table?

Chris

[ssanfu]

My reasoning is that "ActionsTaken" applies to "Issues" not to "Assignments". And usually a date is a good idea.

Wouldn't MOVE the Tech ID (PK) from "Assignments" - that tells what Techs worked which Issue. ADD the Tech ID (PK) to the "ActionsTaken" table.


Table "ActionsTaken"
-------------------------
ActionID_PK (Autonumber)
TechID_FK (Long) (foreign key to Techs)
IssueID_FK (Long) (foreign key to Issues)
ActionTaken (Text or Memo)
ActionDate (Date/time)

A specific Tech records actions for a specific Issue taken on a specific date.


Notice the table name - "ActionsTaken" - "Actions" is plural because there are lots of actions.
The PK field name (ActionID_PK) is singular because it is for one action.