Member Avatar for badmanmc

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

db.JPG 80.55 KB
Edited by badmanmc because: n/a
  • 3 Contributors
  • 3 Replies
  • 94 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pclfw
Member Avatar for timothybard

Here are some recommendations I have:

1) Remove the car_id field from the buyer table; what is the purpose of that field?
2) Create a table to track the make of the car; instead of typing in the make for each car in the car table, I would use a table to track all the makes of cars.
3) Create a table to track the model of each car.
4) Break out the repairs table; I would use one table to track when a person brings in a car for a repair and use another table to track all the parts that need to be worked on during the repair session.

Member Avatar for badmanmc

Thanks!!

Really appreciate the reply it really helps. The reply doesnt just help me with this database but helps me in learning the best practices of database design.

Thanks :)

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.