CODEWITHSUNDEEP
sqldatabasedatabase-design
Vlad
Vlad

Reputation: 475

Best way to store variable number of preferences?

I need to store a variable number of user preferences. For example, if we're talking about movies, user 1 likes movies [A, B, C] and user 2 likes [C, D] etc. What's the best way to store these in a table "properly" - so I can search these preferences efficiently, not have a multitude of tables if there are new types of preferences etc.

Upvotes: 2

Views: 1627

Answers (5)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

-- Predicate: User has id number :user_id.
create table users (
  user_id integer primary key
);

-- Predicate: Movie has id number :movie_id and name :movie_name.
create table movies (
  movie_id integer primary key,
  movie_name varchar(150) not null  -- Movie names aren't unique.
);

-- Predicate: User :user_id likes to watch movie :movie_id.
create table movie_preferences (
  user_id integer references users (user_id),
  movie_id integer references movies (movie_id),
  primary key (user_id, movie_id)
);

If you later have a different kind of preference, say, restaurants, then you need a table of restaurants, and a table of restaurant preferences. 

-- Predicate: Restaurant has id number :restaurant_id and name :restaurant_name,
-- and is known for its :known_cuisine cooking.
create table restaurants (
  restaurant_id integer primary key,
  restaurant_name varchar(150) not null,
  known_cuisine varchar(30) not null
);

-- Predicate: User :user_id likes to eat at restaurant :restaurant_id.
create table restaurant_preferences (
  user_id integer references users (user_id),
  restaurant_id integer references restaurants (restaurant_id),
  primary key (user_id, restaurant_id)
);

You need additional tables for additional preferences, because movies aren't the same thing as restaurants, and "I like 'Top Gun'" doesn't mean the same thing as "I like Burger King."

You won't have a multitude of tables. You'll only have one table per preference. (Because you'll have to implement a table of restaurants to be able to identify them in the first place, right?)

Upvotes: 1

mjv
mjv

Reputation: 75115

I suggest you look into the Entity-Attribute-Value model.
This offers great flexibility with regards to changes to the logical schema and with regards to cardinality.
Various EAV implementations and particulars are discussed in stackoverflow postings, maybe you can start with this one because it generally covers the type of questions asked here.

For example when the application evolves and requires additional kinds of preferences, the physical schema (the supporting SQL tables) do not have to be modified at all, the new preference becomes an entry in the Attribute table.

The main drawbacks with the EAV model are the slightly more complicated table structure and also loss of efficiency (say with million plus entities).
With plain relational model, the data model more readily apparent in the database [physical] schema. Le loss of efficiency comes mainly from the fact that the Values table only stores one Attribute value at a time (preventing the creation of combined indexes etc) and can get rather big, relative to the number of records which would be required for storing the same data in plain relational form.

Edit (regarding performance)
I've been relatively successful with data instances of up to 4 million rows each/most with a dozen attributes on average. The precise "mileage" we can get out of this varies with the sparsity of the data and the relative selectivity of some attribute values. There are several "tricks of the trade" which improve performance, at the cost of further complicating the implementation:

  • storing the most commonly shared, singled-value, attributes within the Entity table rather than (or in addition to) the Values table.
  • using multiple Value tables. Such "partitions" can be driven by data type, Range of Attribute IDs...

Upvotes: 3

Jack Marchetti
Jack Marchetti

Reputation: 15754

Could you create a look up table of sorts, which contains the variable number of preferences?

As new preferences are created, they are put into a table with a corresponding ID.

Then create a join table (many to many) with

UserID
PreferenceID

Upvotes: 0

ChickenMilkBomb
ChickenMilkBomb

Reputation: 945

Sparse Columns! its exactly what they were made for:

http://www.kodyaz.com/articles/sql-server-2008-sparse-columns.aspx

Upvotes: 0

Chipmunk
Chipmunk

Reputation: 348

Have one table with users, one table with movies and a third table (preferences) where you map a user to a movie. Like this a user can like multiple movies and different users can like the same movie. Its basicly a M:N relationship. Is this what you are looking for?

Upvotes: 6

Related Questions