Results 1 to 4 of 4

Thread: design question

  1. #1
    Join Date
    Apr 2003
    Posts
    2

    design question

    Heres the issue:

    I have some 'users' who have 'campaigns' which have 'subscribers'. The way I am currently considering designing the tables for this is a monolithic approach:

    Users (user_id (primary key), first, last, address, etc. )
    Campaigns (campaign_id (primary key), user_id, campaign info)
    Subscribers (subscriber_id (pk), campaign_id, user_id, sub info)

    My question is, since these tables (Subscribers in particular) have a chance to grow very very large, is there any advantage to instead creating new Subscriber tables (say BobsPresidentialCampaignSubscribers) for each User's campaign?

  2. #2
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80

    Re: design question

    No way, if you do so, then I have just one question for you:

    Suppose you want to know all the campaigns that user "X" has attended to, what would you do, make a union query on "n" tables (and which those tables are, the number of table is a variable, so you need an algorithm to first determin which tables should you look into for that user)

    How big the table Subscribers could be: 10000, 100000, 1000000 records? Depending the number of rows that the table can grow to, you could use one DBMS or other (for not more than let's say a few thousands rows you can use Access for instance, but if your table is going to be very big - like millions of rows - you could use a database server like MS SQLServer, ORACLE, PosgreSQL or something like this - deppending of your budget and OS on the server)


    Another thing, is the database design that I'm not confortable with:

    Subscribers (subscriber_id (pk), campaign_id, user_id, sub info)

    Why did you need to insert subscriber_id field. Only to have a primary key in this table? If so, you should consider using the combination campaign_id, user_id as pk of your table (Of course, this works only assuming that a user can be subscribed to a campaign only once - but this is obvious I think)


    ionut

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

    Modelling Marketing Campaigns

    Here's a Model on my Database Answers site that you might find interesting :-
    http://www.databaseanswers.com/data_...igns/index.htm

    It assumes that a Customer can respond only once to each Campaign, and includes the fact that a Customer can buy Products as a result of the Campaign.

    Barry Williams
    Principal Consultant
    Database Answers

  4. #4
    Join Date
    Apr 2003
    Posts
    2
    thanks for the pointers. I realize now more than anything I just need to hit the books - this is a far cry from systems programming.

Posting Permissions

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