[danny-can]

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

[omiossec]

Is for one specif club a member can have One position or many position

[danny-can]

One club member can have many positions in many different clubs.
Many positions exists within a club.


Danny

[omiossec]

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 ?

[aaron_dba]

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