Need help/input with database design

[AccessNooBie]

Hello everyone. I'm new to Access and I've been having trouble grasping the whole normalization thing, hence i'm making this post to see if i'm approaching this database correctly. Here is what i'm trying to do:

Our company has thousands of machines in the field. These machines are sold through distributors. When a distributor or an end-user of the equipment has problems, they contact us. I want to have a database that tracks these incidents when a customer calls with a problem.

I've started on the design and i've attached a PDF that shows how I think the relationships should be. Can someone look at this and tell me if I'm making any major mistakes? I can also paste the list of tables and fields if need be.

Any help would be greatly appreciated.

 

[CraigDolphin]

FWIW, here's my inital quick and dirty response.

I would imagine that an 'incident' table should track the employee handling the call, the person placing the call, the machine causing the problem, the date and time the incident was first logged, and the date and time of the resolution of the problem.

So, my 'Incidents' table might look something like
IncidentID (A/N PK)
EmployeeID (FK)
PersonID (FK)
MachineID (FK)
IncidentReportingDateTime
IncidentResolvedDateTime

You could maintain the incident parts details table easily enough.

I would setup relationships to your incident table with the person table, the machine table, and the employee table.

Now, that assumes only one employee/complainant/machine per incident. if there is a possibility of more than one employee/person/machine per incident then you would need relevant junction tables between your incident table and the other tables (thereby eliminating the need for those foreign keys in your incident table).

The employers of the people etc would be linked via your people table. Ownership of machines would be linked via your machine table.

Hope it helps

 

[AccessNooBie]

FWIW, here's my inital quick and dirty response.

I would imagine that an 'incident' table should track the employee handling the call, the person placing the call, the machine causing the problem, the date and time the incident was first logged, and the date and time of the resolution of the problem.

So, my 'Incidents' table might look something like
IncidentID (A/N PK)
EmployeeID (FK)
PersonID (FK)
MachineID (FK)
IncidentReportingDateTime
IncidentResolvedDateTime

You could maintain the incident parts details table easily enough.

I would setup relationships to your incident table with the person table, the machine table, and the employee table.

Now, that assumes only one employee/complainant/machine per incident. if there is a possibility of more than one employee/person/machine per incident then you would need relevant junction tables between your incident table and the other tables (thereby eliminating the need for those foreign keys in your incident table).

The employers of the people etc would be linked via your people table. Ownership of machines would be linked via your machine table.

Hope it helps

You're correct in your assumption of one machine per incident. There will be multiple employees involved in each indicident though, hence the employee role table. I also need to know what parts are required to repair the equipmenet, hence the part failures details table.

The reason I have the company details related directly to the equipment table is so that I can easily view which customers have what equipment. I also wanted to be able to identify a customer when he/she provide the serial number of the equipment. Knowing that, am I any closer to achieving my goal, or should I rearrange my tables as you suggested?

Thanks a million for your help.

 

[The_Doc_Man]

Search this forum for the terms "Call Desk" and "Call Tracking" and "Incident Tracking" as this topic is very high on our hit parade. Sometimes we just give advice that lets you see someone else's wheel before we reinvent one for you.

 

[AccessNooBie]

Which section should I look in? I didnt see anything in the Theory and Practice of Database Design section.

 

[Rabbie]

Use the search facility at the top of the page. That will search all posts.