I am somewhat of a novice in this area and wonder if one of you experts can tie down a question that has been floating around in my head the last while. When implementing a generalization, what is the best way of doing it.
If I was designing a DB for an online store selling different types of 'product' such as 'book', 'jewellery' etc. what is the best way to 'disciminate' one product type from the other. Should I just include an attribute 'product_type' in the parent entity 'product' or is there a more efficent way of doing this?
I just woner if the repetition of an attribute like 'product_type' has any drawback, if it repeats the word 'book' a 100,000 times. Or is there a way using an SQL statement, to join the specialised 'ISBN' key in the book entity with the 'product_id' attribute in the master entity 'product' to seperate the product types?
yes, often the supertype table has a "type" column
the supertype table has all the common attributes/columns, like name, price, etc.
the subtype tables will then have unique attributes/columns, "unique" in the sense that only that particular type has those attributes/columns, as well as a foreign key linking back to the parent supertype row
one very pragmatic alternative is to use just a single table, with NULLable unique columns
thus, a book row would have NULLs in the clothing columns, and a shirt would have NULLs in the author column, etc.
the advantage of a single table is simpler queries (no joins)