SELECT product from subclass: How many queries do I need?

Posted by Stefano on Stack Overflow See other posts from Stack Overflow or by Stefano
Published on 2010-05-20T16:42:24Z Indexed on 2010/05/20 16:50 UTC
Read the original article Hit count: 200

I am building a database similar to the one described here where I have products of different type, each type with its own attributes.

I report a short version for convenience

product_type
============
product_type_id INT
product_type_name VARCHAR

product
=======
product_id INT
product_name VARCHAR
product_type_id INT -> Foreign key to product_type.product_type_id
... (common attributes to all product) 

magazine
========
magazine_id INT
title VARCHAR
product_id INT -> Foreign key to product.product_id
... (magazine-specific attributes)

web_site
========
web_site_id INT
name VARCHAR
product_id INT -> Foreign key to product.product_id
... (web-site specific attributes)

This way I do not need to make a huge table with a column for each attribute of different product types (most of which will then be NULL)

How do I SELECT a product by product.product_id and see all its attributes? Do I have to make a query first to know what type of product I am dealing with and then, through some logic, make another query to JOIN the right tables? Or is there a way to join everything together? (if, when I retrieve the information about a product_id there are a lot of NULL, it would be fine at this point).

Thank you

© Stack Overflow or respective owner

Related posts about best-practices

Related posts about mysql