Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009

    Is this database structure correct? Please help

    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


    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
    Attached Thumbnails Attached Thumbnails db.JPG  

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by badmanmc
    Do you see any problems in this structure?
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    out on a limb
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2009

    Thanks for the replies!

    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts