Database design

rzwarts · #1 (**permalink**) 01-23-11, 18:48

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

r937 · #2 (**permalink**) 01-23-11, 20:18

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

rzwarts · #3 (**permalink**) 01-24-11, 06:35

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

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

r937 · #4 (**permalink**) 01-24-11, 06:45

Quote:

Originally Posted by rzwarts

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

Hocker · #5 (**permalink**) 02-10-11, 06:12

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.

healdem · #6 (**permalink**) 02-10-11, 06:46

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

Internet Services

Web Development

Digital Marketing

Consumer Tech