Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Question Basic (?) design question

    Hello, I'm hoping someone can help out with something that's probably easy but not to me

    I'm wondering if the following table can be redesign to avoid duplicates:
    user | subscriptionID
    a | 1
    b | 1
    c | 1
    a | 2
    b | 2
    c | 2
    a | 3
    b | 3
    c | 3
    etc...

    the problem for me is that this is a BIG table. I'm talking about 500 million records overall, probably 30 million distinct user entries and about 600 subscriptionIDs.

    from a DB standpoint the problem is that there's a lot of users that have multiple subscriptions so at any point user "a" can have subscriptionID 1,2,3,4,5,6,7,8,9 and so on (600 tops theoretically).

    another issue are the lookups as the application finds records using:
    select user from table where user='a' and subscriptionID=254 (or any other number).

    that complicates (I think) trying to do subscriptionID = 1,2,3,4,5,6,7,8,9 in a single field. I'm not even sure that's valid, efficient or possible.

    Any advice would be greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Quote Originally Posted by alvilla View Post
    I'm wondering if the following table can be redesign to avoid duplicates:
    What do you mean with "duplicates"?

    I'm talking about 500 million records overall, probably 30 million distinct user entries and about 600 subscriptionIDs.
    So what?

    another issue are the lookups as the application finds records using:
    And what issue is that?

    that complicates (I think) trying to do subscriptionID = 1,2,3,4,5,6,7,8,9 in a single field
    Do not put data into a single field that belongs into multiple rows.
    Whenever you have a comma separated list of values in a column, there is something wrong.

    I really don't understand your question (if there is one at all in your post)

  3. #3
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Normally you would simply put a unique index on the columns that you want to be unique. So user and subscriptionID form a unique combination then build a unique index on that combination of columns.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Sure, put a primary key on both fields. E.g.

    Code:
    CREATE TABLE MyLittleTable (
       userId VARCHAR(20),
       subscriptionId INTEGER,
       PRIMARY KEY (userId, subscriptionId)
    )
    Presto... no duplicates.

Posting Permissions

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