Database design - car mechanic (1 Viewer)

[melvis]

Hi all,

Just looking for a bit of advice really. I'm building my dad a database for his garage so he can store customers details, MOT test details, job details. Where I'm getting confused is displaying this information in a form.

Basically, I want there to be one form that shows (without subtables etc) all of the information relating to that particular customer. For example, the form will show the customers details and have buttons that open up previous MOT test for their vehicle and another that opens to show all previous jobs.

I'm ok with the forms part and buttons, but haven't a clue about showing the records I want. The fields I need in the tables are:

Customers Details:
Customer Name
Address
Postcode
Telephone Number
Registration Number

MOT Details:
Registration Number
Customer Name
MOT Date
Passed (yes/no)
Reason for failure
Retested (yes/no)
Next MOT Date

Job Details:

Registration Number
Customer Name
Item1
Item1Cost
Quantity
Item2
Item2Cost
Quantity
(etc up to 20)
Total cost (to be calculated on the form)

Any ideas would be welcome.

Cheers

 

[dsigner]

As I understand it an MOT relates to a car NOT a customer. The current layout will cause confusion if a customer changes cars or has more than one.
How about starting with one for for each table? That is simple and easy to understand and you will see if you have got the correct details and relationships. Once that is working you can get clever with forms which pick a bit here and a bit there.

 

[Pat Hartman]

tblCustomer:
CustomerID (autonumber, PK)
FirstName
LastName
Address
City
State
Postcode
ContactPhone

tblVehicle:
VehicleID (autonumber, PK)
CustomerID (foreign key to tblCustomer)
RegistrationNumber
Manufacturer
Model
ModelYear
etc.

tblMOTDetails:
MOTID (autonumber, PK)
VehicleID (foreign key to tblVehicle)
MOTDate
Passed
ReasonForFailure
Retested

tblJob:
JobID (autonumber, PK)
VehicleID (foreign key to tblVehicle)
JobDate

tblJobItems:
ItemID (autonumber, PK)
JobID (foreign key to tblJob)
ItemName
ItemCost
Quantity

I removed data that was repeated. I fixed the foreign key relationships. I removed embedded spaces and special characters in column names. I added additional fields. I normalized the items table. You can now have an infinite number of items. I added two tables. That should give you a start.