is this a sound database plan?

[bite_tony]

Hi.

I'm no expert on access, but i'm giving it a go. Just wondered if someone would check over my plan to spot any holes in it before i start trying to put it together. I have attatched a plan below.

I am creating a system whereby our company can monitor aftersales service calls. Our company fits windows, and occasionally we need to go back to a job to fix various issues.
I need to be able to to get data on
A)customer details
B) How many calls are being made to individual houses
C) how many calls are being made regarding each issue ('issues' can be separated into calls in about five different catergories)

I'm very rusty with access, it's been a very long time since i used it.
Can someone point me in the right direction?

Thanks

 

[plog]

Forget about everything but tables at this point. Don't worry about forms or queries or reports. Getting your tables properly structured will enable everything else.

I see 4 tables:

Customers
Issues
Calls
IssueCategories

The way these tables connect to one another is through ID numbers, so each table will have an autonumber primary key named like CustomerID, IssueID, CallsID. That means in the Issues tables you will have a field called IssueID and a CustomerID field so that you can tie that issue to a specific customer. The Calls table will have a field called IssueID so that you can tie a call to a specific issue.

Now, I have two questions: Can 1 call be made to handle more than 1 issue?

Is a customer a property or a person?--Suppose I own 2 properties and you do work at both--do I go in the Customer's table twice or just once?


What you need to do now is list every field that should be in each table. Do that and post back what you have.

 

[jzwp22]

Just focusing on your tables, you need to normalize a little more, since you are repeating information in more than 1 table

tblCustomerDetails
-pkCustDetailID primary key, autonumber
-txtSurname
-txtAddress
-txtPostalCode
-txtPhone

tblIssues (just a list of all possible issues, each as a record)
-pkIssueID primary key, autonumber
-txtIssue

tblServiceCalls
-pkServiceCallID primary key, autonumber
-fkCustDetailID foreign key to tblCustomerDetails
-fkIssueID foreign key to tblIssues
-dteServiceCall (date field)

 

[jzwp22]

Pat

I have seen many discussions on naming conventions. They are not required but I find them helpful to me especially when designing queries and coding. As you mention, having to change the datatype of a field could cause a problem if you choose to rename the field as a matter of consistency.

Regarding the service calls, I do agree that a service call could have many issues, so a separate but related table would be in order. Thanks for catching that.

 

[jybrick]

You might also want to include a reference to the person(s) who did the original install so that you might be able to do some tracking on who is responsible for your return calls.

John