By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,576 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,576 IT Pros & Developers. It's quick & easy.

Storing a series of numbers of variable length

P: 7
Hi,

I am designing a table in SQL Server 2008 which will contain, in each row, a list of numbers of variable length. The maximum (10) and minimum (2) list lengths are known. The most obvious approach seems to be just creating 10 columns (num1, num2....,num10) and making num3...num10 nullable. Is there a better way of doing this? It just seems wasteful to have so many columns that will be empty most of the time for most of the rows (the average sequence length is close to 4).

Thanks ahead of time!

ulas
Nov 27 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
there's not definite correct answer really. just recommendations. it would all depend on what kind of table you are creating and how it will be used. it could be a single column with series of numbers as stirng....or create a number of columns or create a single column and insert multiple row instead...

more details, please....

-- CK
Nov 27 '08 #2

P: 7
Hello,

Thanks for the reply and my apologies about the lack of detail. OK let's make it more concrete.

Let's say you are storing playlists for users. There are two tables that you are using for this purpose:

PlaylistPreference: [PreferenceID] SequenceID
Sequence: [SequenceID] Song1 .... Song10

What I am trying to figure out is would it be better to have 10 columns or is there a smarter way of doing this. Each SongN is a FK from the Song table (and in case my funky notation is not clear [..] indicates PK.

Please let me know if you need more information :)

Thanks!

ulas

PS. PreferenceID is also a FK from the Preference table which keeps the core information about different preferences (e.g. how important the preference is, which user it relates to, etc...)
Nov 27 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I'd go for one row per song with sequenceid and songN as primary key.

-- CK
Nov 27 '08 #4

P: 7
Thanks for the reply.

Wouldn't the solution you proposed assume that the ordering does not matter? In my case I care about the ordering so that would require a seperate column (Order) or I guess I could try to use the row ordering in the table if that was guaranteed to be kept consistently in order on SELECT.

Thanks!
Nov 28 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.