Subclass SELECT product: How many queries do I need?

Question

I create a database similar to that described here , where I have different types of products, each of which has its own attributes.

I inform the 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) 

Thus, I do not need to create a huge table with a column for each attribute of different types of products (most of which will then be NULL)

How do I SELECT product product.product_id and see all its attributes? Should I first make a query to find out what type of product I am facing, and then, using some logic, make another query to the JOIN correct tables? Or is there a way to combine everything together? (if, when I get the product_id information, there is a lot of NULL, at that moment everything will be fine).

thanks

Answer 1

You can do all this in one query, several columns will remain 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 application to determine which result set columns you are interested in in any given case.

In terms of performance, this should be pretty fast (foreign keys, indexes); and it creates a consistent set of results for any type of product.

I would recommend not using .* And explicitly listing each column name, it is more portable, more maintainable and less error prone.

Answer 2

Nice design. Good job, avoiding the Entity attribute value trap.

You will just make the connection, as you said, but I do not see the need for two queries. I don’t even think that a 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 m.product_id IS NOT NULL and for web_site, w.product_id is NOT.

Logs only:

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

For websites only:

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

Your big question was getting column names? You probably code them, or use reflection to get them. Most database access layers provide reflection.

Answer 3

Why not create an AttributeDefinition table and a ProductAttribute table? Sort of:

 AttributeDefinition Id Description ProductAttribute AttributeDefinitionId ProductId Value 

Then, no matter what product you come across, you know that you can get all the attributes by simply ProductAttribute table. And you do not need to add a new specific table every time you need a new product with custom attributes.

Answer 4

A big unpleasant union of all, with explicit columns for each type, NULL if they are not applied (greatly 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 the product be displayed as follows:

 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