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

Question

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

Answer 1

Why not make an AttributeDefinition table and a ProductAttribute table? Something along the lines of:

AttributeDefinition
    Id
    Description

ProductAttribute
    AttributeDefinitionId
    ProductId
    Value

Then, no matter which product you are dealing with, you know that you can get all the attributes by simply querying the ProductAttribute table. And you don't have to add a new specific table each time the need for a new product with custom attributes arises.

Answer 2

Nice design. Nice job avoiding the Entity Attribute Value trap.

You will simply do a join, as you suggested, but I don't see a need for two queries. I don't even think the product_type table is required.

SELECT * FROM product p
LEFT JOIN magazine m
ON m.product_id = p.product_id
LEFT JOIN web_site w
ON w.product_id = p.product_id

In the above query, for a magazine, m.product_id IS NOT NULL and for a web_site, w.product_id IS NOT NULL.

Magazines only:

SELECT * FROM product p
JOIN magazine m
ON m.product_id = p.product_id

Websites only:

SELECT * FROM product p
JOIN web_site w
ON w.product_id = p.product_id

Your big question was about getting the column names? You're probably coding these, or you use reflection to get them. Most database access layers provide reflection.

Answer 3

You could do it all in one query, a few columns would stay empty:

SELECT
  t.product_type_name,
  t.product_type_id 
  p.product_id,
  p.product_name,
  p.[common attributes to all products...],
  m.*,
  w.*
FROM
  product p
  INNER JOIN product_type t ON t.product_type_id = p.product_type_id
  LEFT  JOIN magazine     m ON m.product_id      = p.product_id
  LEFT  JOIN web_site     w ON w.product_id      = p.product_id
WHERE
  p.product_id = ?

Use product_type_id in your app to determine which columns of the result set are interesting to you in any particular case.

As far as performance goes, this should run pretty quickly (foreign keys, indexes); and it produces a consistent result set for any product type.

I would recommend against using .* and for explicitly listing every column name, this is more portable, more maintainable and less error-prone.

Answer 4

Big nasty union all, with explicit columns for each type, NULL if they don't apply (vastly simplified):

SELECT ID, ProductType, m.Name as MagazineName, m.Pages as MagazinePages, 
                        NULL as WebSiteName, NULL as WebSiteURL
         FROM Magazines m
UNION ALL
SELECT ID, ProductType, NULL as MagazineName, NULL as MagazinePages, 
                        w.Name as WebSiteName, w.URL as WebSiteURL
         FROM WebSites w

Will product an output like:

ID   Type       MagazineName   MagazinePages   WebSiteName   WebSiteURL
1    Magazine   Time           100             NULL          NULL
2    Magazine   Newsweek       80              NULL          NULL
3    Website    NULL           NULL            Yahoo         www.yahoo.com
4    Website    NULL           NULL            Google        www.google.com