Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Oct 24 '12 at 15:26

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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
share|improve this answer
    
Aaaaaa! It seems so simple, why couldn't I have thought of that?! Thank you very much. –  SavedCoder Oct 24 '12 at 14:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.