Need your input on creating tables

Question

I just want some advice about mysql database. I'm creating a new database for hired cars that need servicing and repairs. I have created 4 tables. Car_id is linked to a primary key table with car information. I was thinking of putting the repair_id and service_id with in the car data table. But that means it will have 3 primary keys in one table. WIll this be okay?

Garage table-

• Garage id (Primary key)
• Garage name
• Mechanics fname
• Mechanics sname
• address
• Phone

Mechanic data-

• Mechanics id (Primary key)
• Garage id (Foreign key)
• Mec name

Repair data-

• Repair id (Primary key)
• Car id (Foreign key)
• Work description
• Mec id (Foreign key)

Service data-

• Service id (primary)
• Car id (Foreign key)
• Works description
• Mec id (Foreign key)

I have slightly altered the table. What do you think. Will it work? Garage data is Garage_id
Garage name fname
surname address Phone number

type data is
Type_code ------------ will be number 1 for repair and number 2 for service Type_description repair/service

mecanics data is Mec id
Garage id
Type _code

Repair_id
Car id - i have created a table already for this
Work description
Mec id

Service id
Car id
Works description
Mec id

Thank you for helping me. It will be useful if you can see if its okay.

Answer 1

repair should have a car_id column. Putting repair_id in car implies that you can only ever have one repair for a car, which I assume isn't a limitation you'd want.

Same thing for service_id.

Answer 2

I would advise a single table for jobs with a reference job types table; this way you can add job types at will, 6mnth service, 12month service, M.O.T, Repair, Refurbishment etcetera. Mechanics should go into their own table, perhaps staff would be better, then you could have a staff type reference table with manager, senior mech, etcetera.

Of course this answer assumes you have a vehicle table.

garage_sites(garage_site_id, garage_name, etc)

mech_staff(mStaff_id, garage_site_id_fk, name, etc)

vehicle_job(vJob_id, vehicle_id_fk, vJob_type_code_fk, mStaff_id_fk)

vehicle_job_type(vJob_type_id, vJob_type_code, vJob_type_description)

You can find a lot of pointers and base database models at:

http://www.databaseanswers.org/data_models/index.htm