sqlite database: advanced SQL or redesign

Question

I've been programming for several years, but I'm pretty new to SQL(ite).

I'm designing a database for a sort of a bookkeeping program. What I've got so far:

orders 
 (o INTEGER PRIMARY KEY, 
 customer INTEGER REFERENCES customers(c), 
 num_products INTEGER, 
 date STRING, 
 discount INTEGER, 
 comments TEXT, 
 status INTEGER)
o_products 
 (o INTEGER PRIMARY KEY REFERENCES orders(o), 
 p_0 INTEGER, 
 p_1 INTEGER, 
 p_2 INTEGER, etc.
o_qtys 
 (o INTEGER PRIMARY KEY REFERENCES orders(o), 
 q_0 INTEGER, 
 q_1 INTEGER, 
 q_2 INTEGER, etc.
o_prices 
 (o INTEGER PRIMARY KEY REFERENCES orders(o), 
 p_0 INTEGER, 
 p_1 INTEGER, 
 p_2 INTEGER, etc.

The customers and products tables are irrelevant.

Now, this design works well for retrieving the products, quantities and prices in an order, but I'm not sure how to do something like figure out total quantity of a product ordered in all orders. I know I could run a lot of queries and sort through the data in my code, but I'm looking for one elegant query, something like SELECT sum(ordered of this product). I could turn the o_products table on its side, to list what orders each product is in, but then it would be hard to retrieve what products are in one specific order. I've done some searching, but I can't seem to find an answer.

Is there a good way to do this, or should I redesign the database? If I should redesign it, how?

Answer 1

I think the tables o_products, o_qtys, o_prices should be combined into a single table, with a one-to-many relationship with the orders table as follows:

order_details
(
 order_id INTEGER,
 order_item_id INTEGER,
 product_id INTEGER,
 price REAL,
 quantity INTEGER,
 PRIMARY KEY (order_id, order_item_id)
 FOREIGN KEY (order_id) REFERENCES Orders(o)
)

When you want to find out the total quantity of a given product, then

SELECT SUM(quantity) 
FROM order_details
WHERE product_id = :product_id

• SavedCoder: Aaaaaa! It seems so simple, why couldn't I have thought of that?! Thank you very much.