SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
Thread: Database design
-
Jul 12, 2004, 06:14 #1
- 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
-
Jul 12, 2004, 06:17 #2
- 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?
-
Jul 12, 2004, 06:19 #3
- Join Date
- Aug 2003
- Location
- Bucks [Uk]
- Posts
- 127
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by 1337-Dev
Thanks for the help
-
Jul 12, 2004, 06:29 #4
- 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
.
-
Jul 12, 2004, 07:27 #5
- 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.ssegraves [at] gmail.com
On Image Use, Abuse, and Where We're Headed
stephan | XMLHttpRequest Basics
flickr | last.fm | Cogentas, LLC
-
Jul 12, 2004, 07:34 #6
- Join Date
- Aug 2003
- Location
- Bucks [Uk]
- Posts
- 127
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by Hartmann
-
Jul 12, 2004, 08:04 #7
- 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).ssegraves [at] gmail.com
On Image Use, Abuse, and Where We're Headed
stephan | XMLHttpRequest Basics
flickr | last.fm | Cogentas, LLC
-
Jul 13, 2004, 03:20 #8
- 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.Dr John
www.kidneydialysis.org.uk
-
Jul 13, 2004, 16:40 #9
- 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
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
-
Jul 13, 2004, 21:57 #10
- 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!
-
Jul 13, 2004, 22:25 #11
- 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?
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
-
Jul 13, 2004, 22:33 #12
- Join Date
- Mar 2003
- Location
- CA
- Posts
- 210
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
what happens if PowerSteering...-ChaCha
-
Jul 14, 2004, 05:31 #13
- 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.
-
Jul 15, 2004, 12:18 #14
- Join Date
- Jul 2003
- Location
- Kent
- Posts
- 1,921
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by ChaCha
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)Dr John
www.kidneydialysis.org.uk
-
Jul 15, 2004, 14:05 #15
- 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