MySQL – How to Store Tax for Products with Changing Tax Rates

If a category has related features then you have to link them independently from product. If a feature is only present through a category then you can omit the link between feature and product. If not, you have to link feature with both category and product, or else you might have inconsistencies between a product category's features and the actual features for that product.

Assuming that a category has a set of features and a product might have specific features (aside from those from it's category), I would suggest the following scheme:

CREATE TABLE Feature (
    FeatureID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100))

-- Each product has a set of categories (N to M)
CREATE TABLE CategoryByProduct (
    ProductID INT,
    CategoryID INT,
    PRIMARY KEY (ProductID, CategoryID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID))

-- Each category has a set of features (N to M)
CREATE TABLE FeatureByCategory (
    CategoryID INT,
    FeatureID INT,
    PRIMARY KEY (CategoryID, FeatureID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

-- Each product might have additional features aside from the one's from it's category (N to M)
CREATE TABLE FeatureByProduct (
    ProductID INT,
    FeatureID INT,
    PRIMARY KEY (ProductID, FeatureID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

So if you want to retrieve a specific product's features you have to traverse through it's categories and particular features:

-- Features by product's category
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN CategoryByProduct CP ON P.ProductID = CP.ProductID
    LEFT JOIN FeatureByCategory FC ON CP.CategoryID = FC.CategoryID
    LEFT JOIN Feature F ON FC.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

UNION ALL

-- Stand-alone features
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN FeatureByProduct FP ON P.ProductID = FP.ProductID
    LEFT JOIN Feature F ON FP.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

Following this approach, you just have to add a category to a product to "inherit" it's features, and you have the possibility to add custom features to particular products. Also changing the features of a category will automatically impact all products related to that category.

regarding question 1): A table with the following fields 1) date ,to set the date-from which the discount is valid 2) product, to define which product we are talking about 3) discount_type, i.e. 1=percentage, 2=how_many_product_for_this_price, 3=price 4) percentage, needed for discount_type=1 5) quantity, needed for discount_type=2 6) price, needed discount_type=3

Example: If you have this data (I will leave out date for simplicity):

product, discount_type, percentage, quantity, price
1, 1, 0.5, null, 42
2, 2, null, 2, 42
3, 3, null, null, 25

product 1 has a discount of 0.5*100 = 50%, and a price of $42

product 2 can be sold 2 for the price of one, so 2 for $42

product 3 has a discount for $25