Bad Design?

[spyderman]

Hi,

I have a table that will be storing information on cars. On my HTML form there is a list of 30 check boxes, one for each feature of a vehicle (ie. power windows, power locks, air, etc....)

My question is, should i create a field in my table for each feature or is there a better way of doing this?

[drawmack]

For normalization concerns you should do it like this

tblCars
ID
Make
Modle
Year

tblFeatures
ID
FeatureName
FeatureDetails

tblFeaturesToCars
ID
CarID
FeatureID

[spyderman]

Drawmack, thanks for the suggestion.
I was considering that option myself, but I'd read that MySQL 's performance will slow significantly if joins are done on large tables. My vehicle table will store approx 30 000 records.

Will it slow down performance dramatically when searches are performed, or am I safe to use joins?

[keith73]

You could have one field, a set type.
It's similar to an enum, but you can have multiple items selected.

Problems with it, maintaining it. If you make changes to the set, it could screw up your results.

• keith

[jjoske]

Use the first option. If you take care with indexing etc you shouldn't notice much of a perfomance hit, and 30,000 records isn't really that big.
The fist option will give you the following advantages:
Easier to add new categories.
If you ever have to easier to migrate to a new database system as it uses standard SQL. The set type isn't standard SQL and wouldn't be available in other database systems.

[drawmack]

Originally posted by jjoske
Use the first option. If you take care with indexing etc you shouldn't notice much of a perfomance hit, and 30,000 records isn't really that big.
The fist option will give you the following advantages:
Easier to add new categories.
If you ever have to easier to migrate to a new database system as it uses standard SQL. The set type isn't standard SQL and wouldn't be available in other database systems.

Yeah when they say large databases they are talking in the hundreds of thousands and even millions of entries. If you know a little predicate algebra (which is what relational databases are built on) you know that every database slows down with every table created for normalization. As a programmer don't worry about this it is not your concern, it is the dm admins concern. If you are one in the same then don't worry about it when you program. You can always through hardware at a database server to make it run faster but good mornalization will keep your code clean.

Here are just a couple of the benefits you get by breaking it out into a features table, aside from what's mentioned above.

Quickly & Easily search for cars with a specific feature set
Give car types a base price and features an increase price and dynamically calculate the price of cars

Sorting based on features is very easy

Deleting a feature does not corrupt your cars table.