Results 1 to 6 of 6

Thread: Database design

  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Database design

    I am faced with a design problem within my database where I end up in a Many to MANY relationship....

    I have two tables (simplified)

    Users: userid,username
    Codes: code,description

    Now I need to keep track of what codes have been handed out to each user as one user may only receive the code once.

    How do I go about that without ending up with one big table ? Is there a smarter way to do this ?

    Raymond

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a third table, into which you will place the ids of the user and the code
    Code:
    CREATE TABLE usercodes
    ( userid INTEGER NOT NULL REFERENCES  users (userid )
    , code VARCHAR(9) NOT NULL REFERENCES codes (code )
    , PRIMARY KEY ( userid , code )
    );
    the PK ensures that each user can get any given code only once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    My problem is that this table will get VERY huge... 10.000 codes times say 500 users...

    Is there a more intelligent solution because I already thought of this...

    Quote Originally Posted by r937 View Post
    you need a third table, into which you will place the ids of the user and the code
    Code:
    CREATE TABLE usercodes
    ( userid INTEGER NOT NULL REFERENCES  users (userid )
    , code VARCHAR(9) NOT NULL REFERENCES codes (code )
    , PRIMARY KEY ( userid , code )
    );
    the PK ensures that each user can get any given code only once

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rzwarts View Post
    Is there a more intelligent solution because I already thought of this...
    you might have thought of it, but you are worrying needlessly, because that ~is~ the intelligent solution

    10,000 codes times 500 users is only 5,000,000 rows, which is a routine medium-sized table

    properly indexed, there is no performance problem

    try it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    2
    Suppose we have the following scheme for our library database (assuming some simplifications). Old loan data are preserved. For every book, we only have one occurrence.
    Book(bno, title, author, publisher)
    Reader(rno, name, address, city)
    Loan(bno, rno, loan-date, return-date)
    This scheme of our library database does not support the possibility to have several occurrences of the same book. Extend the scheme to do so, using SQL/DDL.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    please don't hijack threads, by all means add to the thread if you are propsing an answer, or if you have a seemingly identical problem, but please don't tack on any question onto someone else's thread.

    why?
    becuase the OP may not have got an answer, and your hijack can make thsat even less likely
    some posters base their responses ont he thread title, and they may not see your posting or may not interpret as one they can answer

    so new question, new thread
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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