supertype and subtype, how to implement in MySQL

If you have a database with PRODUCTS and you have some products that share entities (for example: serial number, size, weight), but have some entitities that are unique for some product types (for example: books have an author, CDs have artist, movies have actors) what is the 'best' way to handle it?

From some looking around on the internet I think a solution may be to use SUPERTYPES and SUBTYPES.

PRODUCTS will be the supertype, which will have subtypes of books, CDS, movies.

I'm at a loss for how to implement this in MySQL.

This site has an ERD showing the situation: http://www.databaseanswers.org/tutorial4_db_schema/tutorial_slide_8.htm

I took a stab at it below, but it's missing something (how to tie in BOOKS, CDS, and MOVIES):

PRODUCTS (product_id [pk], product_type_id [fk], title, serial, size, weight)
BOOKS (book_id [pk], author, cover_type)
CDS (CD_id [pk], artist, genre)
MOVIES (movie_id [pk], actor, rating)
PRODUCT_TYPES (product_type_id [pk], product_type {book, cd, movie})

 

BOOKS (book_id [pk], author, cover_type, title, serial, size, weight)
CDS (CD_id [pk], artist, genre, title, serial, size, weight)
MOVIES (movie_id [pk], actor, rating, title, serial, size, weight)


I'm not sure if you need all those atributes in all tables but you get the idea.

 

Using what you have right now, just add 1 column called Product_id_fk to each of these tables: Books, CDs, and Movies.

 

SoKickIt - I'm not sure if you missed my point. Your solution currently doesn't tie into a parent right now? I was thinking to try to reduce replication of common fields in the child tables. Maybe you're saying its easier to just do it that way, but even if I go that route, how to tie it to the parent?

smallbuzz - your solution is what the databasesolutions site indicates. I don't understand that solution. How do you control that any given Product_ID can only have one subtype (book, cd, or movie)? If Product_ID is introduced as a FK in each of (book, cd, movie), couldn't a record be introduced in more than one of these that tie back to the same Product_ID (Product_ID should be able to tie to only one of these).

 

It's not about being easier, there's a redundancy in your/their solution.

For example: In your solution it's possible for something to have a product_type "CD" and be stored in "books" table.

How would you prevent that?

 

If you are thinking about multiple tables for different product types, you are going to complicate a lot of your queries down the road (eg to display all products you will potentially need to link many tables together).
Personally, I think it is far better to just have one product table and make it "wide", eg, add all the specific attributes you need in there.

eg
product (product_id, product_type_id,commonfield1,commonfield2,commonfield3,...,bookfield1,bookfield2,...,cdfield1,cdfield2,...,moviefield1,moviefield2,... etc)

And just leave the ones not relevant for that product as null.

 

I agree with fatmagoo that your query is going to get more complicated down the road, however, the structure will be much more cleaner than mixing all the attributes into one table.

Forget what I said in my last post, I didn't look at the ER diagram. What you want is one to one relationships: product-book, product-cd, and product-movie. Here's how the table should be:
PRODUCTS (product_id [pk], product_type_id [fk], title, serial, size, weight)
BOOKS (product_id [pk], author, cover_type)
CDS (product_id [pk], artist, genre)
MOVIES (product_id [pk], actor, rating)
PRODUCT_TYPES (product_type_id [pk], product_type {book, cd, movie})

Basically, replace the book, cds, movies id with the product id.

To answer your concern about preventing the same product_id showing up in books/cds/movies table, it is not possible to do it in the database, you have to do it in your code. Whatever code you use to add a new product should insert 1 row into product and 1 row into one of the other three tables.