That's what I'd do:
table user (
id primary key,
name,
email,
-- etc
);
table approver (
id primary key foreign key user(id),
appointed_date,
-- etc
);
table appointment (
-- whatever PK
created_by foreign key user(id),
approved_by foreign key approver(id),
-- etc
);
Now any user can create an appointment, but only users also present in approver table can approve it.
Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody.
Table Structure
Table_Offer
--------
ID FK
Name
start time
end time
MandatoryGroup
--------
ProductId (FK to product)
MixGroup1
--------
ProductId (FK to product)
MixGroup2
--------
ProductId (FK to product)
Table_offerDetails
------------------
ID PK
offerId (FK to table_offer)
MandatoryProduct(FK to MandatoryProducts.productID
firstProduct ( fk to MixGroup1.productId)
secondProduct (FK to MixGroup2.productId)
Offer
----
ID name startDate EndDate
--- ---- -------- ------
1 COMBO
MandatoryGroup
-------------
ID name
--- ----
1 A
2 B
MixGroup1
---------
ID name
--- ----
3 P
4 Q
MixGroup2
---------
ID name
--- ----
5 x
6 Y
7 Z
OfferHeader
-------------
ID Customer count(B) B_disc time_disc stat_disc DiscTotal orderPayableTotal
-- --------- ------- ---- -------- --------- -------- ------------
1 BOB 2 20 5 2
OfferDetails
------------
ID offerID MandatoryProduct 1stProduct 2ndProduct
---- ------- --------------- ---------- ----------
1 1 A P X
2 1 A P Y
3 1 A P Z
4 1 A Q X
5 1 A Q Y
6 1 A Q Z
7 1 B P X
8 1 B P Y
9 1 B P Z
10 1 B Q X
11 1 B Q Y
12 1 B Q Z
To construct an offerDetails , you must provide a mandatory , a mixgropu1 and amixGropu2. So caseB , has only 2 offers as follows :
1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z
2B 2Q and 2X =2 offers
1A 1Q and 1X=1st offer
1B 1Q and 1X=2nd Offer ... etc other combinations are vivid.
Other offers are based on business logic:
For quantity offer discount:
create query to find the count of B in an offer for a customer . lets call it QTYB. find QTYB %2 and multiply it with quantityOffer value ( which is $20)
For statistics offer, Just add another bit called earnedPoint for a customer's purchase. and set the earnedPoint an expiry date. earned point can be the same as purchased amount. Check if the purchased amount >=$X , them issue discount as per policy.
Similar is the comparison of order time if its before 5PM and after 10AM then issue 5% else issue 10% discount.
Best Answer
Do not make a table for every product.
This problem has been solved many ways. Try this:
Make a product (or products) table, put your common product attributes in the product table, then make an attribute table and a productattribute table, something like:
Then you can assign arbitrary attributes to your products.
You can take the schema a little further if you like, using:
This technique complicates your schema a bit, so be aware of what you are getting into if you decide to use the extra columns to store values by data type.
An alternative that might just suit your needs a little better would be to use a NoSQL Store like MongoDB for your products. Then you can store the attributes you need for each type of product without resorting to the complexity of a relational database schema.