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

    Red face Design & SQLl Help

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: Design & SQLl Help

    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
    ;
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    8
    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

Posting Permissions

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