-2

Now and then I need a row that refers to a list of integers.

In some of these cases adding whole table feels like overkill. I never need to address these ints outside the context of the row.

My C# code just gets simpler when I don't need to worry about the multiple tables that are underneath.

A quick example would be: a list of Enums that only exist on the C# side.

In theory I'm looking for a 'List of integers' field type. Is there anything there?

User
- Id                 int
- Property1          int
- Property2          int
- ListOfEnumValues   list(int) -- or something

At the moment I fallback to adding the a comma separated string, but this takes more bytes then I would like and feels even a bit more "dirty".

An example how I do it right now:

public IEnumerable<int> Numbers
{
    get { return _NumbersDb.Split(','); }
    set { _NumbersDb = string.Join(',', value); }
}

I'm thinking about writing some kind of C# helper method and use varbinary.

Any other recommendations?

Dirk Boer
  • 8,522
  • 13
  • 63
  • 111
  • 4
    It is called a junction/association table. – Gordon Linoff Jul 08 '19 at 19:04
  • Hi @GordonLinoff, what I want to omit is actually the junction tables - it gives too much overhead for some small cases (like refer to a list of enums that only exist within C#). Of course for all important **"Hero" tables** I do it the proper way. – Dirk Boer Jul 08 '19 at 19:07
  • Are you asking if there's a collection type in SQL? If so, I don't believe there is. – Rufus L Jul 08 '19 at 19:17
  • 1
    Not 100% sure what you are asking. Are you simply looking for a table of integers? – Sean Lange Jul 08 '19 at 19:17
  • you're using 100 bytes for Name, and trying to save some bytes on integers serialized to string?... – Lanorkin Jul 08 '19 at 19:23
  • 2
    If all you ever need is a list of integers that is only ever used as a list over on the C# side, then just make this column a varchar(somethingbigenough) and do your comma separated list. That's a [HUGE RDBMS antipattern](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) though and the next developer may skin you alive, but it sounds like it will fit the bill here. If you go this route it will just be *'dirty'* because it is. A junction table would be more scalable solution and won't suffer from the antipattern issues. – JNevill Jul 08 '19 at 19:24
  • Also assuming general enums can fit in one byte, AND assuming you don't need more than 8 values in list, you can use BIGINT / int64 / 8 bytes for that :) also you can pack that in UNIQUEIDENTIFIER - 16 bytes! will be enough for all uses! :) – Lanorkin Jul 08 '19 at 19:27
  • *"I will never need to..."* is almost always a bad assumption. Don't take a shortcut or implement an anti-pattern; do it right. There should be a table that stores this information. – Daniel Mann Jul 08 '19 at 19:29
  • Also also... if this is a distinct list of integers and the values don't go terribly high then perhaps a bitmask would be appropriate so you just store one number and then pick the bitmask apart on the C# side for your list. – JNevill Jul 08 '19 at 19:30
  • @RufusL, sorry I did it by heart - assignment was of course missing, updated the example. – Dirk Boer Jul 08 '19 at 19:30
  • Hi @Lanorkin, of course this is just a random example. The name field is not relevant for this case so I removed it. Tnx for pointing me to it. – Dirk Boer Jul 08 '19 at 19:32
  • @DanielMann, I hope you agree it's difficult to judge over someone else's architecture from a distance and i.e. it makes a big difference if I'm making a game or missile trajectory software. Being flexible for future change means for me (in this case) not having an extra table. – Dirk Boer Jul 08 '19 at 19:34
  • @JNevill, thanks! That question (+answers) might actually be an interesting read for me. – Dirk Boer Jul 08 '19 at 19:37

1 Answers1

-1

There are no collections in MS SQL Server. Therefore, a possible solution will be stored in NoSQL. For example, in XML or JSON

Evgeniy Gribkov
  • 485
  • 4
  • 8