Hi,

First a little bit of an introduction: I am a student at Uni and have embarked on a project over the summer to increase my skills as a DB designer I am creating a database for a company that moves vehicles on trucks/transporters.

I have been using a CASE tool to get my ER diagram together which has helped a lot as this is the largest amount of tables I have dealt with before.

The main entities I have created and their participation constraints are as follows:

• A customer can make a quote
• A customer can request for a job to be done which is a number of vehicles from one place to another at a cost per vehicle
• A job consists of 1 or more vehicles
• A vehicle can have a make
• A vehicle can have a model
• A vehicle can be delivered by a transporter
• A vehicle can have additional comments attached some of which may be chargeable
• A vehicle is delivered by a driver
• There can be 1 or more drivers handling the vehicle in a fixed amount of ways
• A driver can handle a vehicle by either collecting it, delivering it or handling it.
• A vehicle can be damaged
• Damage is allocated to a particular driver who did the damage
• Damage consists of 1 or more chargable items
• Jobs can make up an invoice
• Invoices have a tax code

The are a couple of problems I have found (I am sure you will find many more!):

• Vehicle model depends on vehicle make but if I put the both of them into one table I have the make repeated many times
• I am not sure damage is hooked up correctly to the intersection table.
• Should a number of the customer attributes be separated off to other ables? particularly things like email addresses which will be mostly unused but is a handy attribute for the future. (possibly email-based invoicing etc)

That's about all I can think of right now! Please feel free to ask me any questions about my design.

I have been continuing on this DB design and with the help of posts here have been able to learn a bit too

If you see the re-submitted image I now have the vehicle make and model joined up differently so that a Vehicle Make can apply to many Vehicle Types and the model and foreign key make the primary key.

Now would this make sense to have a surrogate key in the VehicleType table?
The way I see it, I am going to have many repeated makes and models in the Vehicles table and using a surrogate key would cut 1 field off this table.

Also in the Job table I have the surrogate ket jobId. If this was taken away there would be no way to uniquely identify the record as even using all the remaining attributes as the primary key would not guarantee uniqueness. What is done in those situations?

Thanks

_z0

"Vehicle model depends on vehicle make but if I put the both of them into one table I have the make repeated many times"

if you want to validate Make/model combinations, you better keep them in the same same.

Any progress with this design?

Hi,

Yeah, I am progressing ok with the design. Just a couple of problems however.

1) I have a From and To attribute. Now these locations could well be repeated across a number of jobs. Would it be worth splitting these off into a separate places table with an intersection table in-between? With normalization as far as I can see you can go OTT with it have lots of tables which when you do a n-way join will become really slow. DB lectures said try to avoid doing over three joins where you can but I can see already I will be doing at least 5 to generate an invoice. What are your thoughts on this?

2)When jobs have been invoiced etc they are locked so no changes can be made. they are only ever required for viewing and for using to print copy invoices. Would it be a good idea to purge these records into a history table? I'm just thinking that users do not want to browse through old records unless the specifically ask to. The only way I can see to eliminate invoiced jobs is to look for jobs which have no corresponding invoiceID. Is keeping all records in the same table ok in terms of design and performance?

Thanks again

_z0