Hi Folks

Designing a database for an address book which includes the person title within the organization. Note that they can have two titles...

CREATE TABLE address_book (
id int NOT NULL auto_increment,
sectioncat1 int (1), <--- position id for first title
sectioncat2 int (1), <--- position id for second title
fname varchar(30),
lname varchar(50),
address varchar(50),
etc....
PRIMARY KEY (ID)
);

The second table:
CREATE TABLE sectioncat (
id int NOT NULL auto_increment,
positiontitle varchar (50) not null,
position char(1) NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO sectioncat VALUES ('1', 'Chairperson', 'E');
INSERT INTO sectioncat VALUES ('2', 'Vice Chair', 'E');
INSERT INTO sectioncat VALUES ('3', 'Past Chair', 'E');
INSERT INTO sectioncat VALUES ('4', 'Secretary', 'E');
INSERT INTO sectioncat VALUES ('5', 'Treasurer', 'E');

Now is where I'm having the problem , trying to produce a directory listing... Such as

Chairperson
Name
Address
etc..

Vice Chair
Name
Address
etc...

Any help would be appreciated... Should I denormalize the address_book table or does someone know what sql statement I need to produce the desired recordset.

Thanks
Danny

Hi

CREATE TABLE address_book (
id int NOT NULL auto_increment,
sectioncat1 int (1), <--- position id for first title
sectioncat2 int (1), <--- position id for second title
fname varchar(30),
lname varchar(50),
address varchar(50),
etc....

pid VARCHAR(2) references, <-- add a new column corresponding
to id int , in sectioncat Table.

PRIMARY KEY (ID)
FOREIGN KEY (pid) REFERENCES sectioncat (id) <-- add a Foreign key
that refer to sectioncat table.
);


Now use the query ,

SELECT positiontitle
, name
, address
FROM address_book a
, sectioncat b
WHERE
a.pid = b.pid
GROUP BY
positiontitle
;

Thanks for the info...

I guess I should have mentioned that I'm using MySQL 4.0.16 Isam which doesn't appear to support foreign keys... or Referenctial Integrity...

I guess it only leaves denormalizing some of my data... or convert to INNODB? Just startig to using MySQl DB server!!!! I don't know what the trade-off are from using ISAM versus INNODB?

Any other suggestion would be appreciated.

Danny