SQLite - storing multiple values

Question

Hi, how can I store and retrieve in SQLite database multiple values for the same row of the same column?

i.e. I have a product column and another column is stores, where I put in all the stores, where it is possible to get this product:

Product: iLamp;
Stores: River's; McWay; Lonnie's; ...

How can I implement this?

Thank you in advance.

Answer 1

I would use something simple like JSON. However this is bad db design since it is not normalized.

Answer 2

Either store as a comma-separated list for each store or add multiple rows one for each pair "Store"-Product".

Answer 3

If you're smart, you won't do this. Because when it comes time to figure out which stores stock the item, your queries will be hideously deformed. So will those of your stock control application when they try to insert and delete stores. What you'll end up with is what I like to call SQL gymnastics, spending more and more time trying to do SQL in the most bizarre way, simply due to a bad design choice.

Seriously, store these in different rows in the database, as Codd intended.

It's far easier (and faster in terms of the DBMS grunt) to combine multiple rows into a single semicolon-separated string than to break that string into elements.

A schema such as this would suffice:

Products:
    ProdCode integer primary key
    ProdDesc varchar(50)
Stores:
    StoreCode integer primary key
    StoreDesc varchar(50)
StockLevels:
    StoreCode integer \
    ProdCode  integer / primary key
    Count     integer

Answer 4

like others have mentioned, you could store it as a comma separated string, and then put that in your database, but like ocdecio mentioned, the way you have your tables right now is bad a design. You should consider doing something like adding another table, like a PRODUCT_TO_STORE table that has two columns, one has the key of a product, and the other column has the key to a store. Then there is a relationship between products and stores, and helps normalize your data, which in most cases, is a good thing. Then when you need to find all the stores a product is in you could just perform a query on that PRODUCT_TO_STORE table.

Answer 5

change the schema.

do not store multiple values in the same row's column.

add a new table where the multiple values can be stored in their own rows

bad table deisgn:
parents:

ParentID, values, other columns

good table design:
parent

parentID, other columns

child

parentID, childID, value