products and configurable_products in postgresql

Question

Hi

I have a Product table and a ConfigurableProduct table. If there are several variations of the same product like a shirt in different colors I create a ConfigurableProduct. When a user is looking at the catalog he should see a list of products unless there is a ConfigurableProduct, then he should see it with a select box for each variations.

How do I structure the tables for Product and ConfigurableProduct and how do I query the db so I can page through the results?

Thanks

Answer 1

I am going to answer this as if you do not have tables created. I am not sure if that is true though.

The following is a simple example, but I assume you have more data.

products

• id
• name

configurable_products

• id
• variation
• product_id REFERENCES products(id)

You can just make the configurable products a reference to products.

If you want a listing of products with their configurations then you can do:

select p.name, c.variation 
from products p left outer join configurable_products c 
     on (p.id = c.product_id);

Of course you can just search for all the configurable_products based on the product id too when needed.

As for the paging part of your question you will have to clarify what you mean. You can use limit to limit results if you don't want to get everything at once.