Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8

    Angry Unanswered: MYSQL Many-to-Many relationship help

    Hi Folks

    Just a newbie looking for some help using MySQL verions 4.016. Here is a DB design with three tables: Members (members address_book), Clubs (list of clubs,addresses,etc) and Positions (list of positions). The relationships between the three tables are as follows:

    Club may have many Members...
    Members may belong to many Clubs...
    Members may have Many positions with different Clubs
    Clubs may have many Positions from many Members

    -------------------------
    CREATE TABLE members (
    memberid int(11) NOT NULL auto_increment,
    fname varchar(50) default NULL,
    lname varchar(50) default NULL,
    ....other fields ....
    PRIMARY KEY (id)
    ) TYPE=MyISAM;

    --------------------------------
    CREATE TABLE clubs (
    clubno int(12) NOT NULL default '0',
    name varchar(30) NOT NULL default '',
    contact varchar(30) NOT NULL default '',
    ....other fields .....
    PRIMARY KEY (regno)
    ) TYPE=MyISAM;

    --------------------------------

    CREATE TABLE positions (
    posid int(2) NOT NULL auto_increment,
    positiontitle varchar (50) not null,
    PRIMARY KEY (ID)
    );

    sample data for positions
    INSERT INTO position VALUES ('1', 'Chairperson');
    INSERT INTO position VALUES ('2', 'Vice Chair');
    INSERT INTO position VALUES ('3', 'Past Chair');
    INSERT INTO position VALUES ('4', 'Secretary');
    INSERT INTO position VALUES ('5', 'Treasurer');
    INSERT INTO position VALUES ('6', 'Executive Director');
    etc....

    ----------------------------------

    Do I need to create a fourth table to relate my three main tables.

    CREATE TABLE relation (
    memberid int(11) NOT NULL,
    clubno int(12) NOT NULL,
    posid int(2) default NULL,
    ) TYPE=MyISAM;

    I need to be able to select all the executive members for each club with their titles...

    Any help would be appreciated?

    Danny

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Is for one specif club a member can have One position or many position
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8
    One club member can have many positions in many different clubs.
    Many positions exists within a club.


    Danny

  4. #4
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Originally posted by danny-can
    One club member can have many positions in many different clubs.
    Many positions exists within a club.


    Danny
    This not my question for one club (not all), can a member of this club have many or one position ?
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  5. #5
    Join Date
    Dec 2003
    Posts
    6

    Re: MYSQL Many-to-Many relationship help

    Sounds way to much like a homework assignment - so a quick pointer instead of an answer. Reread about 3NF, focusing on partial key dependancy.

Posting Permissions

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