I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work.
Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category ids in my table and be able to select rows based on the categories they belong to. I believe this is called a many to many relationship. I am using sqlite.
You will need an intermediary table, where each row is a item and a category.
ItemID Category 111 1 111 2 222 1 222 2 222 3 333 3
To select all items based on a category (say category 2), you could do the following query
SELECT * FROM Items AS I INNER JOIN ItemsInCategories AS N ON N.ItemID = I.ItemID WHERE N.Category = 2
And this would return
ItemID 111 222
A many to many relationship should be accomplished using a mapping table. Not by hacking a multi-valued column type
For example this BlogPost <-> Category sample:
Blog
Id
Title
Content
Category
Id
Title
Blog_Category
BlogId
CategoryId
This means that when BlogPost with Id 12, is part of Category 3,5 and 10, Blog_Category contains these rows:
12, 3
12, 5
12, 10