So I’m working on a product catalog, and I’ve decided to use a ‘types’ table to help shorten the number of null columns in my database (see diagram below).

The diagram shows 5 tables:

product_line - the seasonal line of products (A grouping, like 2006/2007)

product_sex - the sex of an individual product (mens, womens, unisex)

product - the individual product (assigned a lineID and a sexID)

product_detail - detail about a product (dependent on product_detail_type)

product_detail_type - the types of detail that are inserted into product_detail

Now, here’s where I’m getting a little muddled: When I’m trying to get the product detail back for a product, some or all of the product_detail_type options may/may not be filled (eg: some products may not have sizes, or may not have features). I’d like to, in a single query, get back the product details for each product.

I know I can use some joins to get data back, but the returned values need to be the detail_type.type (eg: content=>”some content”, features=>”a list of features”).

I know I can do this - I’ve done it a hundred times before (specifically a current project that uses a global ‘types’ table extensively), but sometimes you’re so close to your solution you can’t see it. Forest for the trees, my friends.

This entry was posted on Sunday, December 30th, 2007 at 3:09 pm and is filed under code, web. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.