Database design

[bloo_fish]

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

[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?

[bloo_fish]

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

[1337-Dev]

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 .

[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.

[bloo_fish]

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... ?

[Hartmann]

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).

[Dr John]

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.

[ChaCha]

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...

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
{
....
}

[r937]

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!

[ChaCha]

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]

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.

[hurricane_sh]

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.

[Dr John]

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)

[ChaCha]

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.