467,910 Members | 1,669 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,910 developers. It's quick & easy.

Storing a series of numbers of variable length


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!

Nov 27 '08 #1
  • viewed: 1690
4 Replies
Expert 2GB
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

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 :)



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
Expert 2GB
I'd go for one row per song with sequenceid and songN as primary key.

-- CK
Nov 27 '08 #4
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.

Nov 28 '08 #5

Post your reply

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

Similar topics

14 posts views Thread by Luiz Antonio Gomes Pican?o | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.