design question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

design question

Author

Topic

pdb
Starting Member

3 Posts

Posted - 2003-01-03 : 08:34:47

Hi all,

I have a design question, I'll try to explain it with an example.

Let's say you have a cardealer site where users can enter all kinds of criteria in their profile and choose to be notified when a car is in stock that fits their criteria.

I want to have a set of predefined options which they can enter

e.g. 'Fuel', diesel, petrol
'Transmission,' manual, automatic
'Color', red, blue, green

People can enter none, one or multiple criteria for each item. For example they can choose 'diesel' as the fuel AND manual and automatic transmission and all the colors available (via a multiselect)
I think you get the general idea.

I was thinking of creating a table 'Preferences'

UserID, PreferenceType, PreferenceValue

So in the mentioned example I would add the following to the table

1, 'F', 'Diesel'
1, 'T', 'Automatic'
1, 'T', 'Manual'
1, 'C', 'Red'
1, 'C', 'blue'
1, 'C', 'green'

I can write a SELECT query like this:


SELECT * 
FROM StocksTable S
WHERE Fuel IN (SELECT PreferenceValue FROM Preferences WHERE PreferenceType = 'F')
AND Transmission IN (SELECT PreferenceValue FROM Preferences WHERE PreferenceType = 'T')

etc etc.

seems that it should work, but I can't stop thinking there must be a better way.

Any opninions?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-03 : 09:00:07

If PreferenceValue is unique, I think you can do away with the PreferenceType ...

Jay White
{0}

pdb
Starting Member

3 Posts

Posted - 2003-01-03 : 09:04:03

It's not guaranteed unique...

But the overall idea is OK? There aren't any better techniques to do this?

X002548
Not Just a Number

15586 Posts

Posted - 2003-01-03 : 09:09:34

In a normalized model, it would be nice to know about the type of preference, but I agree with the other post about doing away with it if it's unique. In addition your sql in your original suggestion is "held hostage" to any new types (another words every time a new type appears you have to rewrite the query). You created a denormalized table and should leverage the benefits it affords.

For example, take all of the preferences the client requests, load them in to a local variable data type of table (or permanet table if you need to retain their preferences), and then join to your table.

Good Luck

Brett

pdb
Starting Member

3 Posts

Posted - 2003-01-03 : 09:16:41

quote:
For example, take all of the preferences the client requests, load them in to a local variable data type of table (or permanet table if you need to retain their preferences), and then join to your table

I'm not sure I get this.

Let's say I have a table called 'Cars'

CarID, Brand, Fuel, Transmission, Color

and I want to select all cars that fit the preferences for UserID '1' as mentioned in my previous post. How would you join the preferences table?

GreatInca
Posting Yak Master

102 Posts

Posted - 2003-01-03 : 18:25:56

It might be unique it might not be. I would assume it would not be.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-01-03 : 20:23:16

pdb,

Here is my 2 cents....

First I would make "Options" tables.

Option Table
Option (Key) eg. Fuel

Options table
Option (Key) eg. Fuel
Options (Key) eg Diesel

Then I would split your "Cars" table by turning columns into rows..

Cars table
CardID (Key)

CarsOptions table
CarID (Key)
Option (Key)
Options (Key)

Then your user table

UserPreference table
UserID (Key)
Option (Key)
Options (Key)

After that it is a intersect (or divide) query. Adding new options is now trivial.

HTH

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources