mysql discount table

[hosefo81]

my tables are as below.

CREATE TABLE PRODUCT

(

PROD_ID VARCHAR(5) NOT NULL,

PROD_NAME VARCHAR(20) NOT NULL,

PROD_DESC VARCHAR(20) NOT NULL,

PROD_PRICE DECIMAL NOT NULL,

PROD_CAT INT NOT NULL,

PRIMARY KEY(PROD_ID),

FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY,

);

CREATE TABLE SALES

(

SALES_ID INT NOT NULL,

SALES_DATE DATE NOT NULL,

CUST_ID VARCHAR(5) NOT NULL,

EMP_ID VARCHAR(6) NOT NULL,

PRIMARY KEY(SALES_ID),

FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER,

FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE,

);

 

CREATE TABLE SALES_DETAIL

(

SALES_ID INT NOT NULL,

PROD_ID VARCHAR(5) NOT NULL,

QUANTITY INT NOT NULL,

PRIMARY KEY (SALES_ID, PROD_ID),

);

 

if some of the product have discount for a duration of some time, should i create a product table?

CREATE TABLE DISCOUNT

(

DISCOUNT_ID INT NOT NULL,

PROD_ID VARCHAR(5) NOT NULL,

START_DATE DATE NOT NULL,

END_DATE DATE NOT NULL,

);

 

This is my sql queries to count total sales without the discount table. if the discount table is created, how am i going to make changes to the query to count the total after deducted the discount?

select month(s.sales_date), s.cust_id, sum((p.prod_price * sd.quantity)) as Total

from product p, sales_detail sd, sales s, category c

where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and c.category_id=p.prod_cat and year(s.sales_date) = \'1999\'

group by month(s.sales_date), s.cust_id;