Hi, I need to design a daily (or not) newspaper db. Several (20) subscription options will be offered, such as subscribing only on Mondays and Saturdays, dayly, etc, and after selecting one option, the subscriber will be allowed to choose different delivery addresses compatible with his subscription option. So if Monday Thursday and Sunday subscription option is selected, there will be 3 days same address, 2 days one address and one other address, and 3 days different delivery addresses options.
I imagined "hard tabling" everything, using tables like Monday delivery address for Monday/Thursday/Sunday subscription and Monday/Thursday+Sunday delivery, but this will represent hundreds of tables and maybe someone has a more ellegant idea or a link.
Thanks, I had thought something like that. But the ideal would be a table design that could be ported between DBMS and could prevent not so smart telemarketing staff from entering an address for Monday, Wed and Fri and another for Monday for the same subscription with as little programming as possible. so I will need an associative like:
With two tuples like this:
Plan Mon/Wed/Fri - Delivery Mon/Wed?Fri => one address form
Plan Mon/Wed/Fri - Delivery Mon, Delivery Wed, DeliveryFri=> 3 address form
Well, the easy way to prevent the user from entering Monday twice would be a unique constraint on SubscriptionDay( subscriberID, Day ), if indeed that was not already the primary key.
Use pre-defined plans if you find that more appropriate, but try to do so in such a way that the final information recorded resides in just a few tables. You surely do not want a different table for each plan to record the same information - that sounds like chaos!
Maybe you want a plan table that holds the valid delivery options. The user selects the plan, and the input screen then adjusts to prompt for only the relevant information (e.g. 1 address, or 3 addresses). Then store the information (including the type of plan selected) in the generalised table structure:
That would be my preference. I can then query accross all subscriptions (e.g. how many deliveries do we make on Fridays) without having to UNION together the results of n queries, where n = number of plans.
BTW, I don't follow your point about "a table design that could be ported between DBMS" - what's non-portable about this approach?
Hi, I think this last suggestion is the best. Thanks again. About porting, I understand all solutions discussed here are portable, I was just emphasizing I did not know what would be the DBMS. I will join all addresses in one table, including the billing address, which can be another one.
[QUOTE][SIZE=1]Originally posted by laqa
>Hi, I need to design a daily (or not) newspaper db. Several (20) >subscription options will be offered, such as subscribing only on >Mondays and Saturdays, dayly, etc, ...
Check out this Newsagents Data Model on my Database Answers web site :- http://www.databaseanswers.com/data_...ents/index.htm
It could easily be extended to handle multiple addresses for a Customer by having an Address ID field as a Foreign key in the Customer Deliveries table, pointing to a new 'Customer Addresses' table.
If you would like me to draft something, let me know.