Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Database/Table design problem..

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

Posting Permissions

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