I am fairly new to mysql and wanted to practice making a database and tables. As well as filling the tables with made up data and then practicing queries. I wanted to emulate a real life database project.

So I decided to make a database for a Car Dealership.

the database consists of three tables...Car...Buyer..Repairs. The structure is displayed below.


CAR table -

car_id int(5) primary key
make varchar(15)
model varchar(15)
year int(4)
origin varchar(15)
buyer int(5) foreign key references buyer_id in the BUYER table
cost double(5,2)



BUYER table -

buyer_id int(5) primary key
name varchar(20)
address varchar(20)
tel int(10)
car_id int(5) foreign key references car_id in the CAR table


REPAIRS table-

part_id int(5) primary key
origin varchar(15)
cost double(5,2)
car_id int(5) foreign key references car_id in the CAR table


(SCREENSHOT ATTACHED)


Do you see any problems in this structure?
anything I should have done differently?
anything I could do to improve database performance?

Please bare with me as I am new to this and wish to be a DBA one day soon!

All comments will be appreciated!!

Thanks people

yes -- you have CAR with a foreign key to buyer, and BUYER with a foreign key to CAR

one of those is wrong

also, p.s. "bear with me" means have patience, but "bare with me" means let's get undressed together

OK so lets start with some basics
Car is a separate entity
So is buyer
So is a repiar.. all well and good
but can a customer buy more than one car
can a car be owned by more than one customer (if you don't care about the past then probably not, but if you want to know who had waht repairs done to what car over time then you do need a mechanism to know who owned what cars over what period. redesigning both entities should allow you to store data relevant to that transaction eg the date, the value and so on. look up intersection tables

I suspect your repairs table needs some thought
its unlikely that a car will need only ever one part to make a repair, a repair may consume many parts, it may also consume labour (again several different types of labour).
would a part come from a standard library of parts or would you want to create each part each and every time you use it.

Thanks people!!

Really appreciate the replies they are a big help. The replies dont only help me with this database but help me in learning the best practices of database design.

Thanks