Database Structure Help

[kevinwong1]

Hi Everyone,
I am an Alphasoftware beginner and am trying to develop a database to keep track of vehicle maintenance (record oil change, tire rotation and other vehicle related services). Here is my structure:
Work_Order_hr
|--->CUST_ID (one-to-one linking by cust_Id)
|==> VEHICLE (one-to-many linking by WO_ID)
|==> MAINTCE (one-to-manyh linking by license_plate)

My problem here is when I enter a new WO for the returning vehicles, the existing maintenance record get overwritten instead of adding a new record to the new WO. I've tried this new structure... and it seems to work, but is too easy, and am wondering if this is the right away to structure the WO set.


Work_Order_hr
|--->CUST_ID (one-to-one linking by cust_Id)
|==> VEHICLE (one-to-many linking by WO_ID)
|==>MAINTCE (one-to-many linking by license_plate)

I am eager to hear from you and any input is greatly appreciated!

Kevin

[Keith Hubert]

Re: Database Structure Help

Kevin,

Do you own a vehicle? If yes, what is the order of questions your garage asks you to book a car in for service? If you dont own a vehicle, phone up a garage and pretend you do, listen carefully to the questions you get asked. after you tell them that you want to book your car in for a service. This will then enable you to understand your set structure.

[GGandhi]

Re: Database Structure Help

hello

when you design any table that table should have an id for the records to be identified ( usually referred to as primary key) unless that table will have only one record.
this is what happening to you. you do not have an id for the records.
common structure will be
table A
id integer auto increment primary key
...
.. rest of the fields
table B
id integer auto increment primary key
...
...
rest of the fields
any foreign key reference to another table for example table A.id
then when you enter new data
the data will not be overwritten, just a new record will be added.

notice I use integer for primary key but alpha will automatically suggest character. you can use it or change to numeric. that is your choice.

[Skipperj]

Re: Database Structure Help

field id as Ghandi said.
Then: Cars/trucks are usually identified by a serial number the is 8 or 12 or newer at 17 digits. Vey important information! some RV's may go as long as 24. It depends on what you are doing. Usually, you can set a lookup or other on the last 6 digits or better, the last eight. There are even programs out there that can break down the serial number to give yr, make, model, style, engine, and much more.
Point is: you may find yourself needing the serial number and it should only need to be entered one time for each vehicle.

[Ted Giles]

Re: Database Structure Help

In the UK, taxis have a plate on the back with this sort of information.

TAXI REGISTRATION
Manufacturer
Model
Colour.

FR 13 CTG
Skoda Octavia
Blue.

This will help when you start printing worksheets, to identify the vehicle. Especially the colour.

[Al Buchholz]

Re: Database Structure Help

Kevin

You should review the AlphaSports invoicing example.

From that you should see that you should have a WorkOrderHeader and a WorkOrderLine table.

The WorkOrderLine table can then use field rule lookups to get the appropriate information about the vehicle and appropriate maintenance for it.

Hope that helps.

[kevinwong1]

Re: Database Structure Help

Thank you all for contributing your kind suggestions/thoughts. Big thanks to Ganhi, I was missing a primary ID. I've got it figure out at last. Moving on...