Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Unanswered: MySQL Database Design Setup?

    I have question regarding MySQL design. For my website I'm interested in selling vehicles. I'm going to be providing the year, make and model of the vehicles. The visitor/user is going to be filing in the rest of the information ie (price, mileage, transmission, color, location etc ....). I'm thinking about setting up my database in this manner, please tell what you think. Me (the admin) is going supplying the year, make and model.

    I was thinking about setting up my database in one of two ways.

    First way,

    In one column I would have the
    ID,
    year,
    make,
    model,
    I'll call it the tbl_car_description

    In the second column I would have the user information.
    User ID,
    username,
    location,
    email etc ....
    I'll call it the tbl_user

    In the third column I'll have
    ID,
    tbl_car_description ID,
    tbl_user ID,
    price,
    mileage,
    timestamp,
    location
    and a few other variables the user will type in.

    The second method I was thinking about was this.

    Have a year column
    Have a make column
    Have a model column

    Join the three above Columns with LEFT JOIN statements, creating the year, make and model. Side question, after creating the joins, how would I get the ID? I'll call this the tbl_car_description.

    In fourth column I would have the user information.
    User ID,
    username,
    location,
    email etc ....
    I'll call it the tbl_user

    In the fifth column I'll have
    ID,
    tbl_car_description ID <--? When joining columns how would I get the unique ID?
    tbl_user ID,
    price,
    mileage,
    timestamp,
    location
    and a few other variables the user will type in.

    What do you guys think?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on what you are using, what the objective of the assignement is
    I would follow the rules of normalisation
    Fundamentals of Relational Database Design -- r937.com
    and
    The Relational Data Model, Normalisation and effective Database Design
    are worth a look

    as for tables I'd probablyhave a tabel for users
    a table for stock
    a table for manufacturers
    manufacturers models
    modelbuild standard or some other entity so I could tell the customer what the vehicle had
    possible another entity for additional accessory types and yet another for accessory types fitted (and a description) of those accessories
    ..and so on.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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