Storing a series of numbers of variable length

[ulas]

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

[ck9663]

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

[ulas]

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

[ck9663]

I'd go for one row per song with sequenceid and songN as primary key.

-- CK

[ulas]

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!