How to store a list of items on a single column in SQL Server 2008

Question

I am looking for an efficient way to store users' information in a SQL Server 2008 table.

In the design all users data will be stored in a series of columns in a single table but one of the columns requires to store a list of values, for example: 'column1' will store the username, 'column2' will store the userID and 'column3' will store a list of items that will change over time.

I want to avoid putting this information on a different table for each user because that would require a lot of tables in the database.

What data type should I use for this 'column3'? Should I use a completely different approach?

I see that on SQL Server we can use the table data type, but this seems to work only for temporary storage.

Thank you.

Answer 1

I think you need a different approach.

If you are trying to model a many-to-one relationship then you should use an extra table with a foreign key constraint.

If you are trying to model a many-to-many relationship you can use three tables, with one table being a junction table that links the two other tables together.

If you are looking for a flexible schema you might want to consider an Entity-Attribute-Value approach. This design has its downsides but I think it's better than storing lots of different types of value in a single field.

Answer 2

Generally storing multiples values in column is an indication of poor database design. It makes it very difficult to efficiently select rows based on criteria within that single column. Having said that, if you really only ever need to select those values on a per row basis then consider using XML as SQL Server natively supports XML data.

Answer 3

I want to avoid putting this information on a different table for each user because that would require a lot of tables in the database.

If I understand you correctly, and you want to store a list of items for each user, then it requires only one more table, not "a lot". Create a master table (Users) and a details table (UserItems), and add a foreign key column in UserItems that will point back to the owning Users record.

That is the proper way to design entity relationships in a relational database.

Answer 4

It looks like what you have in your requirement is to at the very least store a userID, username and a dynamic list of items for a user. You would need a schema which can hold the necessary information you need. A simple schema can allow for very fast retrieval and complex schemas can have a lot of integrity built in. There are pro's and con's for both different designs, and depending on what your requirement is, one schema design would work better than another.

Here are some examples that you can consider (user - item schema relationship):

many - many relationship schema

• requires that a user to have at least one item list
• if xml format changes many updates will be needed
• users can have the same items as other users

• will get you a list of items for a user without the need of any joins

  User
  userID
  userName
  Items (xml data type)

one - many relationship schema example

• requires that a user to have at least one item list

• restricts users having the same unique item

  User
  userID
  userName ListItemID

  List
  ListID
  ListItemID
  ItemID
  Item

one - many relationship schema example

• restricts users having the same unique item

• does not require a user to have one or many items

  User
  userID
  userName

  Item
  ItemID
  UserID
  Item

many - many relationship schema example

• allows for a user to share the same item that other users have
• it can be very fast to retrieve information
• lots of redundant information. user and item information is stored multiple times
• if the details of a user changes, you will need to make updates to one or many records

• if the details of a item changes you will need to make updates to one or many records

  UserItems
  UserItemID
  userID
  userName
  ItemID
  Item

many - many relationship schema example:

• this allows for a users to share the same unique item
• does not require a user to have a list of items
• it requires joining 3 tables to get information on the items the users have
• if user information changes only 1 record to update
• if the details of an item changes, only one record to update

• if user item list changes, the number of changes would be the number of updates/deletes/inserts needed

  User
  userID userName

  ItemList
  ItemListID
  UserID
  ItemID

  Item
  ItemID
  Item

many - many relationship schema example:

• this allows for a users to share the same unique item
• requires that a user have a list of items (one or more)
• it requires joining 3 tables to get information on the items the users have
• if user information changes only 1 record to update
• if the details of an item changes, only one record to update

• if user item list changes, the number of changes would be the number of updates/deletes/inserts needed

  User
  userID userName ListItemID

  List
  ListID ListItemID
  UserID
  ItemID

  Item
  ItemID
  Item

Answer 5

There are many cases where you may need to store multiple values in a single column; options, attributes etc. are good examples, and it's not a poor database design at all. It's been quite a few years since the question was asked but here's an option to help future seekers. This is based on a fixed number of values/combinations, and it uses bit-masking approach.

--create a reference table 
create table attributes
(opt smallint, descr nvarchar(40))
go
--insert integer rep. of bit-positions: 1,2,4,8,16,32...  
insert into attributes values (1,'KD')
insert into attributes values (2,'FSC')
insert into attributes values (4,'S1S')
insert into attributes values (8,'QTR')
go

A function can help simplify selection of the combination of attributes:

-- This returns a single description value for all combinations
create function getAttribute(@t smallint) returns nvarchar(80) 
as
begin
   RETURN
   STUFF((select  descr+' ' from attributes where 
   (opt&@t=1 or opt&@t=2 or opt&@t=4 or opt&@t=8 OR opt&@t=16) 
   FOR XML PATH('')
    ),1,0,'')
 end
 go

The function can be also used in a computed column as follows:

ALTER table example add attr_descr as dbo.getAttribute(attr)

So the column attr_descr contains a list of the attributes selected for a specific record, whereas attr holds the integer value.

Example:

select dbo.getAttribute(5)

returns: KD S1S

Answer 6

You can also use the XML type. Check here for more information.

Answer 7

If you're using PHP, or another equally capable language, you could store the values as a delimited list, seperated with a special character like pipes ('|'), or even commas if you're sure they won't appear in the actual values you're storing. Extracting the values out would then be easy (in PHP at least) - simply do an $var = explode("delimiting_char", $input_string). You would end up with $var being an array of values. The delimiting_char would be your pipe or whatever you want to use.

Hope this helps (at least some)