0

I've got a situation with minibus repair database design, where it looks like in the Repair table I could do with a 3-composite primary key: jobID from the JobType table (types of maintenance with price and time),
mechanicID (Mechanic table), minibusID(minibus table). This way, I've got type of job performed on the minibus, who fixed the bus, and which one it was. Besides that I intend to add to the Repair table dates (start and finish), and price.
Is it ok to make all the 3 components primary key, or is it better to add another column such as repairID? Or maybe I should find other way to design it?

2
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by katties
0

Let's sort this out.

JobID = primary key in table JobType,
mechanicID = primary key in Mechanic and
minibusID = primary in minibus.

repair table: jobid, mechanicid, minibusid, start-date, end-date, price

It really depends what you want to do. If you want to be able to query your table on any one of the keys then PRIMARY KEY (jobid, mechanicsid, minibusid) is not the answer. It will give you performance increases on searches that specify (jobid) or (mechanicsid, minibusid) or (jobid, mechanicsid, minubusid) but not if you're only searching on mechanicsid or minibusid for example.

Inserts and replace will go much slower.

If you want to search on all three columns individually, just create indexes on all three columns and make them unique.

Edited by colweb: n/a

0

Thank you. I'll add repair_id, it will make my life easier.

0

Ps. It's a student homework and they don't ask for creating indexes.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.