SELECT product from subclass: How many queries do I need?
- by Stefano
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
