Results 1 to 2 of 2

Thread: DB design help

  1. #1
    Join Date
    Jul 2003
    Posts
    10

    DB design help

    I am developing a DB for bulk mailing

    The requirements are

    1. There are categories and subcategories. These two have many to many relationship. ie. One category can have 0 or many subcategories and one subcategory can exist in more than one category.

    Ex.

    Textile
    Accessories
    Garments
    Leather
    Footwear
    Accessories
    Garments

    Manufacturing

    Automotive
    Aerospace
    Footwear

    Education
    Schools
    Colleges
    Training institutes

    Hospitality

    etc.

    A person ( email id) may belong to more than one category and subcategory

    ie.

    rana@hotmail.com may belong to

    Textile - Garments
    Textile - Accessories
    Leather - Garments
    Education - Trainig institutes

    There will be a set of newsletters which has to be sent to the clients.Care has to be taken that eventhough rana@hotmail.com exists in more than one category the same newsletter( Ex. newsletter1) should not be sent to him more than once. The scenario is first newsletters will be sent to Textile category which includes rana@hotmail.com .Second time when the same newsletter is sent to the category Education that should send mails to all email ids in that category excluding rana with the possible warning message that the newsletter has already been sent to rana@hotmail.com.And finally the system should have an detailed report of the mails and newsletters sent.

    The current DB design is

    category table

    cat_id ------------------PK
    category ---------------varchar(200)

    Subcategory table

    sub_cat_id ------------PK
    sub_cat ---------------varchar(200) PK


    emails Table

    email_id ------------PK
    name
    address
    etc...
    lists (to which he belongs to)

    lists in emails table is stored as
    //cat_id1-sub_cat_id1//cat_id2-sub_cat_id2//cat_id3-sub_cat_id3//


    newsletter table

    news_id ------------PK
    newsletter ( path to the newsletter file is stored )


    reports Table

    email_id
    sent_on
    sent_items

    sent_items is of the format
    ///path of newsletter1-Date Sent///path of newsletter2-Date Sent///

    I would like to know about the flaws and the suggestions on improving the design.

    Thanks in advance

  2. #2
    Join Date
    Jul 2003
    Posts
    10

    Re: DB design help

    Sorry these are the category and subcategory details

    Textile----Accessories
    Textile----Garments


    Leather----Accessories
    Leather----Garments
    Leather----Footwear

    Manufacturing----Automotive
    Manufacturing----Aerospace
    Manufacturing----Footwear

    Education----Schools
    Education---- Colleges
    Education----Training institutes

    Hospitality----no subcategory

    etc.

Posting Permissions

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