Hi,
I'm trying to design a database model where by i have a table of members:

members
- id
- name
- email
- etc

and have another table of programs they can join:

programs
- id
- name
- website
- etc

and i want to be able to keep track for each 'member'
a) whether they have joined each program or not; and
b) what their username is for each program if they have joined.

There is going to be anywhere from 0 - 5000 programs and lets just say 0 - max(int) members. The only way i could see this working is having the 2 tables as above and then creating a table for each program ie

program_(%programs.id%)
- members_id
- joined_flag
- username

and linking the program id to the table name using php as that is what the site will be. but i have been told this is not only slow but restricted to MaxNoOfTables variable set on the server. Is this true? What is another solution to this problem?

Thanks,
Michael

I think you'd be better off creating an intersection table which associates a memberID with a programID, and stores any information unique to that pairing. EG:-

membersprograms
MembersID: FK to members.id
programID: FK to programs.id
UserName: usernam oif member on this program
datefirstuse:
datelastuse:

the pk would be a composite of membersID & ProgramID.. that way round you can only ever have one record per user per program, but multiple programs per user, and multiple users per program.

you can find members who haven't joined a program by doing a left join and identifying those memebrs with a null program or those progams with a null memberid.

having a table for each program is not a very efficient design. it means every time you add a program you have to make changes to the db schema and change program logic. finding who is or isn't a member of a program on the face of it is straightforward, but it does mean that potentially your have thousands of different queries.

have a try at this, then populate your tables and see ig you can get the join syntax to work correctly

HTH

Thanks healdem,

This was one approach I thought of, but for some reason I couldn't do this in fear that that table might run out of rows, but now I don't remember why. lol. But yes I think I will do it this way. Thanks for your time and quick response.

Regards,
Michael.

tables never run out of rows

you are allowed to have over a million jillion bazillion rows

your database might run out of disk space, but your table will never run out of rows

your auto_increment might run out of numbers if you choose the wrong datatype, e.g. TINYINT UNSIGNED will run out of numbers after 255, BIGINT UNSIGNED will run out of numbers after 18 quintillion, 446 quadrillion, 744 trillion, 073 billion, 709 million, 551 thousand, 6 hundred and 15...

... but your table will never run out of rows

On epossible modification you may care to think about is userid's.

I can't speak for others, but I try to use the same userid in as many applications as possible. just for convience, and to make it easier for hackers once they've sussed my passwords.

so you could take a view as to whether you should have a userid table, with an intersection table to members, and use the pk from the intersection table to reaplce the memberid. but its down to application logic. I wouldn't use the userid as the pk, unless members cannot change a userid. that wold allow a single member to have multiple userids, using multiple programs, or multiple userids using one program