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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 design question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdb
Starting Member

3 Posts

Posted - 01/03/2003 :  08:34:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/03/2003 :  09:00:07  Show Profile  Reply with Quote
If PreferenceValue is unique, I think you can do away with the PreferenceType ...

Jay White
{0}
Go to Top of Page

pdb
Starting Member

3 Posts

Posted - 01/03/2003 :  09:04:03  Show Profile  Reply with Quote
It's not guaranteed unique...

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

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/03/2003 :  09:09:34  Show Profile  Reply with Quote
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

Go to Top of Page

pdb
Starting Member

3 Posts

Posted - 01/03/2003 :  09:16:41  Show Profile  Reply with Quote


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?

Go to Top of Page

GreatInca
Posting Yak Master

USA
102 Posts

Posted - 01/03/2003 :  18:25:56  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
It might be unique it might not be. I would assume it would not be.

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/03/2003 :  20:23:16  Show Profile  Reply with Quote
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.."
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000