Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    13

    Question newspaper subscription db

    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,

    Luis.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: newspaper subscription db

    How about:

    Subscriber( SubscriberID, ... );
    SubscriberAddress( SubscriberID, AddressNo, ... );
    SubscriptionDay( subscriberID, AddressNo, Day );

    i.e. a Subscriber can have 1 or more Addresses, and one or more SubscriptionDays. Each SubscriptionDay can be associated with a different SubscriberAddress.

  3. #3
    Join Date
    Nov 2002
    Posts
    13

    Arrow

    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:

    PlanID, DeliveryID,

    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

    What do you think?

    Thanks again,

    Luis.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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:

    Subscriber( SubscriberID PK, PlanID... );
    SubscriberAddress( {SubscriberID, AddressNo} PK, ... );
    SubscriptionDay( {subscriberID, Day} PK, AddressNo );

    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?

  5. #5
    Join Date
    Nov 2002
    Posts
    13

    Thumbs up

    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.

  6. #6
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42

    Re: newspaper subscription db

    [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.

    Barry Williams
    Principal Consultant
    Database Answers

  7. #7
    Join Date
    Apr 2003
    Location
    Nanjing, China
    Posts
    5
    How about this solution:

    Subscriber(SubscriberID PK, Name... );
    SubscriberAddress(AddressID PK, SubscriberID FK, Address...);
    SubscriptionDay(SubscriptionID PK, AddressID FK, Day, ...);

  8. #8
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42
    Originally posted by forrestyuan
    How about this solution:

    Subscriber(SubscriberID PK, Name... );
    SubscriberAddress(AddressID PK, SubscriberID FK, Address...);
    SubscriptionDay(SubscriptionID PK, AddressID FK, Day, ...);
    This looks like it should work, if my interpretation is correct.

    Barry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •