SQLite - storing multiple values

Question

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

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