SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: Database design

  1. #1
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database design

    Ok, Iím designing a database for a car company; I need to store information about their cars in a database. The database will be filled in via a web based form, but i am unsure of how to design it.

    it will have all the basic fields of a car, but then what do i do with the extras? like

    Latest model
    Vat Qualifying Car
    Alarm
    Metallic paint
    Leather
    LPG
    Left hand drive
    Central locking
    Alloy wheels
    Cruise control
    Air conditioning
    Climate Control
    Power steering
    ABS

    Etc...

    Do I create a field for each one of these in the database? or is there a better way that i donít know of?

    Thanks for any help

  2. #2
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to seperate information into seperate catagories like this, yes you use different fields accordingly.
    phpMyAdmin is the best for this thing.
    Do you need code help on inserting into the database or not?

  3. #3
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 1337-Dev
    If you want to seperate information into seperate catagories like this, yes you use different fields accordingly.
    phpMyAdmin is the best for this thing.
    Do you need code help on inserting into the database or not?
    No, I can do all the web based stuff, just a little new to database stuff, think i might sub categorise stuff though

    Thanks for the help

  4. #4
    <? james('rules'); ?>
    Join Date
    Jun 2004
    Location
    Wales, UK
    Posts
    788
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suppose you could do that. Its a matter of choice and you need to think what will suit you better when dealing with it in the long term .

  5. #5
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    What about an options table that only holds those fields along with the car ID?

    You would then use 'true'/'false' to tell if that car has those options or not.

  6. #6
    SitePoint Zealot bloo_fish's Avatar
    Join Date
    Aug 2003
    Location
    Bucks [Uk]
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hartmann
    What about an options table that only holds those fields along with the car ID?

    You would then use 'true'/'false' to tell if that car has those options or not.
    Thanks for the suggestion, but i'm asking what would be better db design, would that be better? more efficient at all etc... ?

  7. #7
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Well, it would allow you store all of those options and then use your query to sort all of the information.

    Since there is no way to group those items then I would say yes, the design that I suggested would be sufficient and would probably fufill your needs better than another (you would only need to hold one record per vehicle).

  8. #8
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    optionsused table
    carId, option id (joint PK)


    and
    options table

    optionID, description (PK = optionID)

    car---<optionsused>---options

    A car can use one or more options and an option can be used in one or more cars, so it's a many to many, so the intermediate link table, optionsused, solves the problem.

    This way you can add new options at any point without generating lots of empty fields of nulls for existing cars and without having to alter an existing table's structure.

    This isn't a "better" way, it is the correct way.

  9. #9
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    CA
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    use a bit mask

    This is by far the most efficient solution and, in my experience, simply the best. Whenever you have a series of options that can be implemented in any permutation use a bit mask...
    Code:
    OptionName        OptionInt      BinaryValue
    -------------------------------------------
    AirConditioning      1                0000000001
    PowerSteering      2                 0000000010
    FourWheelDrive     4                0000000100
    SkiRack               8                 0000001000
    Then use the bitwise | operator to create combinations of these values...

    MyCarOptions = AirConditioning | PowerSteering | FourWheelDrive

    ... will yield 7, or in binary 0000000111, which signifies that it is a vehicle with AirConditioning, PowerSteering, and FourWheelDrive

    MyCarOptions = AirConditioning | FourWheelDrive

    ... will yield 5 or in binary 0000000101, which signifies that it is a vehicle with AirConditioning and FourWheelDrive

    To decipher whether a car's options include a particular option, use the bitwise & operator. It will return true (actually it will return the number) if a particular bit has been set on the bit mask..

    if (MyCarOptions & FourWheelDrive) // it has four wheel drive
    {
    ....
    }
    -ChaCha

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    oh my freaking $deity

    is that a violation of 1nf we have there?

    what happens if PowerSteering is no longer a valid option? doesn't every freakin row in the table need to have its BinaryValue bits bumped over one spot?

    i know! i know! it's all perfectly okay because the row has a surrogate key that never changes!!!!!

    feh!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    CA
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is that a violation of 1nf we have there?
    Why yes it is! Glad of you to notice. Can't beat this option for performance, though, can you? And show me an easier more elegant model to work with in your application code. In fact, you will find that even Sql Server herself uses this method in the sysXXX tables.

    Now, I am sure you are alluding to my earlier posts when you rightly question the normalization (or lack of) in this design, but you gotta' pick your battles. Consider the color of the car, for instance. Any one color is made up of the combination of other colors, right? Would you create a separate table called ColorWheel, which had the primary colors as columns, then use numeric values to represent the combinations of primaries that made up the color you are offering the car? No. Color is exactly analogous to the concept of amentities in this case.
    -ChaCha

  12. #12
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    CA
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what happens if PowerSteering...
    No, you just ignore it. It was an option at one time. In fact you should have another table called Options_Vehicles which relates which options are available per Vehicle model.
    -ChaCha

  13. #13
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Middle Earth
    Posts
    41
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't use bit operation, the only benefit is saving tiny cheap disk space, but make everything much more complicated, the source code and database.

  14. #14
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ChaCha
    No, you just ignore it. It was an option at one time. In fact you should have another table called Options_Vehicles which relates which options are available per Vehicle model.
    Which brings you back to the answer that I posted. When you try to use "clever" methods things are likely to go wrong if you are an ordinary user.

    Solve a many to many probelm the normal way. Then if you get stuck, people will be able to understand what you are doing and help you. (You may not recognise it as a many to many problem, but trust me, it is)

  15. #15
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    CA
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand it is a many to many problem. I have implemented this system both ways, the conventional m:m relational way and using the bitmasks. So long as I don't have a common need to query the database on users who meet a certain privilege criteria, then all works great.

    Microsoft suggests using such a system in a request intensive environment, because of the performance benefits. Except if you want to talk about denormalization, they suggest a pipe delimited string of privileges or roles. I think that 'bit twiddling' is much more suitable.. Like it or not, but sometimes in the real world you end up denormalizing for performance benefits. The benefits that the consuming application derive from such a system are that it makes it much easier to check the privileges that a user posesses using bit operations. It also is more consistent with the way people traditionally expect privileges to be implemented.
    -ChaCha


Bookmarks

Posting Permissions

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