Member Avatar for katties

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
  • 404 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by katties

Dani AI

Generated

Three quick points that build on 's note and on 's final choice.

A 3-column composite primary key is perfectly legal and well within MySQL limits: InnoDB permits up to 16 parts in a multicolumn index, so three parts is fine. (dev.mysql.com)

If the three columns truly form the natural, immutable identifier for a repair row, you can declare them as the PRIMARY KEY. Downsides: every foreign key that refers to Repair must include all key columns, and a composite PK prevents multiple rows with the same (jobID, mechanicID, minibusID) — so it stops repeated repairs of the same job by the same mechanic on the same bus unless you include a date in the key. Also, a PRIMARY KEY is implemented as a unique index (columns are implicitly NOT NULL). (linuxtopia.org)

Practical alternative (recommended for most apps, and simplest for homework): use a small surrogate integer PK and enforce the natural uniqueness with a UNIQUE constraint. Keep single-column indexes for queries that will search by mechanic or minibus. InnoDB uses the primary key as the clustered index, so a short integer PK keeps secondary indexes smaller and faster. (dev.mysql.com)

Example — composite PK approach:

CREATE TABLE Repair (
  jobID INT NOT NULL,
  mechanicID INT NOT NULL,
  minibusID INT NOT NULL,
  start_date DATE,
  end_date DATE,
  price DECIMAL(7,2),
  PRIMARY KEY (jobID, mechanicID, minibusID)
) ENGINE=InnoDB;

Example — surrogate PK + natural-unique:

CREATE TABLE Repair (
  repair_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  jobID INT NOT NULL,
  mechanicID INT NOT NULL,
  minibusID INT NOT NULL,
  start_date DATE,
  end_date DATE,
  price DECIMAL(7,2),
  UNIQUE KEY uk_natural (jobID, mechanicID, minibusID),
  INDEX (mechanicID),
  INDEX (minibusID)
) ENGINE=InnoDB;

Recommendation: for a real system use the surrogate repair_id + a UNIQUE constraint unless the three columns are guaranteed to be the single, permanent identifier for the event.

Recommended Answers

All 3 Replies

Member Avatar for colweb

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 because: n/a
Member Avatar for katties

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

Member Avatar for katties

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, networking, learning, and sharing knowledge.