Is this database structure correct? Please help

[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

[Hack]

What one field links all three tables together?

[glafmitzpery]

Good try for one of your first efforts! But needs some modifications:

1) Should not have Buyers & Cars pointing to eachother. Have only one pointing to the other. Integrity of the data is paramount. Look at this scenario:

in car table:
car_id 10
buyer 20

in buyer table:
buyer_id 20
car_id 30

Which is right? Believe me, if you build in the possibility of data integrity errors, they WILL materialize.

So which table should reference the other? Should cars reference buyers or vice-versa?

2) Well, what if a buyer buys more than one car? Are the 2 below the same buyer? The computer will not think so!

buyer_id 10
name bob smith
address 123 main st
tel 2225551111
car 10

buyer_id 15
name smith, bob
address 123 main street apt 7
tel 2225551111
car 20

So, ideally, buyers table should not reference cars. You can also make this decision based on the fact that a buyer can buy more than 1 car, but a car can only have 1 buyer. So we have:

CAR table -

car_id int(5) primary key
make varchar(15)
model varchar(15)
year int(4)
origin varchar(15)
askingprice double(5,2)
purchaseprice double(5,2) (unless the buyer is a real sucker, the price actually paid is usually not the "asking price" from the car table)
purchasedate date
buyer int

BUYER table -

buyer_id int(5) primary key
name varchar(20)
address varchar(20)
tel int(10)

Now you can have a single buyer in the buyers table (with one set of demographic info) with multiple vehicle purchases.

3) How about if you want to run a report to determine your best-selling car model? Are the 2 models below the same?

car_id 20
make honda
model civic 4-door
year 2000

car_id 30
make honda
model civic dx
year 2002

So, instead of a single car table, have a master car table which includes make & model (ideally, it's better to separate this even more into a master makes table & a master models table)

make table -

make_id int pk
name varchar(15)

model table -

model_id int pk
make_id int references make table
name varchar(15)

car table -

car_id int pk
model_id int references model table
year int

I won't go into the repairs tables now, but it needs to be made more granular in a smilar way.

Good luck & this is a great way to start!

[badmanmc]

omg..THANKS!!!

I dont even know where to begin to thank you. That not only helps my database..but helps me learn the concepts of database design.

I really appreciate the in depth reply..I wasn't expecting that.

thanks a lot

[pinswitiz]

you use SQL checking error