Hi!
I'm strugling with this task for my wife's business. They produce a local weekly ad paper and they want a db to mange their business. I'm having trouble with designing the "Product" table properly. This is how their pricing works:
[Ad size] --------[1 week]--------[4 weeks]--------[8 weeks]
--1/2---------------$10 -------------- $9---------------$8
--1/4--------------- $5---------------- $4--------------$3
.
.
.
And so on.
The "1 week" filed is for your ad going in one week. If you commit to 4 weeks or 8 weeks they give you a discount and you pay less PER ad. If this is all I was working with it would be fairly easy to make a product table.
I would make a table for each of the datas: Size, Duration and type (classified or ad) since they can always add or remove these in the future. And then in my "product" table have a combo box that is based on a query of the above tables. This qury will have the "dataName" of each of the above tables. So when clicking on the drop down arrow I would get a long list of all the possible combinations: "Type, Size, Duration".
The problems start here:
They have a size called "Full Page" which can only be comitted to one week so there's no 4 or 8 week option there.
They have Classifieds which have the 4 and 8 week option but they are all a fixed size.
Any ideas?
Also, if you think my design is totally off I would appreciate input.
Thanks
Ami