Generalization+prices history

Question

Suppose I have a product which consists of 3 main items. The product is Laptop, and the items are: screws (id, code, description) buttons (id, code, description) coverage (id, code, description, color).

Each laptop consists of any combination of these items.

1. I can put all into one table "Materials" and add an attribute called "type" to distinguish each item, but then there will be many rows with empty "color" values.

   I thought of specialization: Materials (id, code, description), and then all are subclasses of it. But then buttons & screws classes don't have anything different than the mother class.

   So what are your ideas?

2. I want to add unit price attribute. I need the actual price, and I want to keep a history of older prices on a monthly basis (i.e. each price is bound to MM/YYYY).

   I can create a prices table:

   prices (id, price, date)
   

   But should I keep the actual=current price in the materials table or simply in the prices table?

Answer 1

/* keep your general product specs here: */
PRODUCT
id primary key
type
code
description
standard_price


/* or add a subtype table for some of them: */
COVERAGE_PRODUCT
id pk fk PRODUCT
colour 


/* products are composed of other products: */
PRODUCT_STRUCTURE
part_of not null references product(id),
composed_of not null references product(id)
primary key (part_of, composed_of)

You can keep your historical pricing in your data warehouse, or add a product_price table. in this case, remove "standard_price" from product.

PRODUCT_PRICE
product_id fk product(id)
from_date
price
to_date (nullable)
primary key (product_id, from_date)