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?

Recommended Answers

All 3 Replies

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.

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.