Hi,

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?

Thanks

do a search for supertype/subtype

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)