Relationship problem

[hdgirl]

i have been asked to create a database to record customer complaints and am unsure how to seperate the fields into tables and form the relationship.

There are not to many fields just ID number, Date, Department, Category, Short Description, Full description, customer details, action taken & response.

i have thought that maybe i should create two tables one to incluse Id date & department then all the rest in another and create the relationship between department & ID.

does anyone agree or disagree with this or offer any advise.

thx


CJB

 

[PHV]

For me at least 4 tables:
Departments
Categories
Customers
Complaints


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Advice:

read The Fundamentals of Relational Database Design

Leslie

 

[hdgirl]

Thanks both. if i use 4 tables what will i form the relationship on? as there are going to be many departments with many complaints?

CJB

 

[PHV]

May one complaint belongs to many departments ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hdgirl]

no because one complaint would belong to only one department?

CJB

 

[PHV]

So, no problem.
In the Complaints table you simply have 3 Foreign Keys referencing the 3 other tables respectivly.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

hdgirl

You might want to add tables...

ComplaintHistory
- Although you may feel there may be only one entry for a complaint, adding a history table would allow you to track multiple entries. In my experience, use of a history table makes it easier to depict a time-line of events. True, one memo field may allow you to do this but less effectively.

You are tracking complaints. Should you also track the resolution?

ResolutionType
- Allows you to categorize your resolutions. Moreover, the ComplaintHistory table can now capture the development of the resolution. I wish resolutions could be easy and simplistic, but more often, the resolution may sometimes involve multiple call-backs. Or the customer may not be satisfied with the first resolution.

Are you going to track ownership of the problem / resolution? You have a complaint -- who took the call, who worked on the resolution, who was involved with the escalation. What if you escalate the call to a third party such as your supplier? You may want to track the people involved with the resolution.

And lastly, is your complaint database part of ISO 9000 or similar activity. If so, do require management approval on the complaint / resolution?

PHV pointed you to one excellent source for normalization of a relational database. Here are two other sources...

283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard