Tallidar
asked on
Gift Certificates and DB Structure
I am planning a website that will sell jewelry and other women's accessories. I want to offer gift certificates/cards so people can purchase them as gifts or for use at a later date.
I have a 'product' table and I believe gift certificates should have a 'product_id' just like everything else. I think I will create a 'gift_certificate' table in order to store 'gift_certificate_code' (The special code that you enter to use a gift card toward a purchase) and 'gift_certificate_amount' (the amount remaining on the gift card.)
I am having a hard time figuring out what I need to do with this gift certificate table. I have an 'ordered_products' table that stores ordered items (imagine that.) Gift certificates have attributes described above that normal products do not have. So, how do I store gift certificates in the 'ordered_products' table? The 'ordered_products' table currently has a 'product_id' field which is a foreign key for 'product_id' in the 'product' table.
Does anyone have any suggestions here? I just need some ideas to get my brain going again. I will think about this some more while I wait for a reply.
Thanks experts!
I have a 'product' table and I believe gift certificates should have a 'product_id' just like everything else. I think I will create a 'gift_certificate' table in order to store 'gift_certificate_code' (The special code that you enter to use a gift card toward a purchase) and 'gift_certificate_amount' (the amount remaining on the gift card.)
I am having a hard time figuring out what I need to do with this gift certificate table. I have an 'ordered_products' table that stores ordered items (imagine that.) Gift certificates have attributes described above that normal products do not have. So, how do I store gift certificates in the 'ordered_products' table? The 'ordered_products' table currently has a 'product_id' field which is a foreign key for 'product_id' in the 'product' table.
Does anyone have any suggestions here? I just need some ideas to get my brain going again. I will think about this some more while I wait for a reply.
Thanks experts!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wasifg,
I have a gift_certificate table which stores gift_certifiate_code already. It also stores gift_certificate_amount (the amount remaining for this gift_certificate.) I never thought of storing information for each order that is paid for with a gift_certificate but I suppose this would be good for accounting purposes.
I would definitely like to be able to use part of a gift certificate on one order and then have some left over for future orders. I think I will store how much is left in my already existing gift_certificate table.
Do you have any other thoughts?
I have a gift_certificate table which stores gift_certifiate_code already. It also stores gift_certificate_amount (the amount remaining for this gift_certificate.) I never thought of storing information for each order that is paid for with a gift_certificate but I suppose this would be good for accounting purposes.
I would definitely like to be able to use part of a gift certificate on one order and then have some left over for future orders. I think I will store how much is left in my already existing gift_certificate table.
Do you have any other thoughts?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you both for your help with this. You both made me think about a couple of things I had not thought of before.
ASKER