SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Organization-Affiliation Database

    Hi,

    Long time no posting to the forum. Long time having done a db schema, as well.

    This is the database I'm planning on making. I have a bunch of rosters for organizations which the elite of society like to be associated with. Here's an example: 2007 Council on Foreign Relations Membership Roster [PDF]
    The 2007 list above, has quite a number of members, and the years before that have a similar number but the membership slightly varies from year to year - same goes for other elite org affiliations.

    I want to start entering in the people one at a time, and eventually gather a large database of people and tie them into rosters for, say, the CFR or the Trilateral Commission and exactly what year they appeared on the list. A similar list for Trilateral Commission for 2005 can be seen here, just to get an idea of what I'm going to be tackling.

    OK, so here's the initial DB structure I came up with:

    Code SQL:
    CREATE TABLE Org (
        id SMALLINT NOT NULL AUTO_INCREMENT,
        Name VARCHAR (50) NOT NULL,   
        PRIMARY KEY (id)
    )
     
    CREATE TABLE OrgRoster (
        id SMALLINT NOT NULL AUTO_INCREMENT,
        OrgID INT NOT NULL,
        RosterYear CHAR (4),
        PRIMARY KEY (id)   
    )
     
    CREATE TABLE Person (
        id INT NOT NULL AUTO_INCREMENT,
        fName VARCHAR (20) NOT NULL,
        mName VARCHAR (30) NOT NULL,
        lName VARCHAR (30) NOT NULL,
        Birth CHAR (4),
        Death CHAR (4),
        PRIMARY KEY (id)
    )
     
    CREATE TABLE PersonBio (
        id INT NOT NULL AUTO_INCREMENT,
        PersonID INT NOT NULL,
        Dossier TEXT NOT NULL,
        PRIMARY KEY (id)
    )
     
    CREATE TABLE Affiliation (
        id INT NOT NULL AUTO_INCREMENT,
        Comment VARCHAR (255),
        OrgRosterID SMALLINT NOT NULL,
        PersonID INT NOT NULL,       
        PRIMARY KEY (id)       
    )

    I will be implementing it in MySQL 5.

    I just need someone to check over the structure to see if it's ok normalization-wise, etc. Organizations I plan on including, some of them began before 1905, as well as the birthdate for the members, so I didn't use the date type.

    Any help would be appreciated. Thanks.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    change all of the auto_increment columns to unsigned

    remove id from OrgRoster and make the PK (OrgID, RosterYear)

    why is PersonBio its own table?

    remove id from the Affiliation table and make the PK (OrgRosterID, PersonID)

  3. #3
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. Much appreciated.

  4. #4
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    remove id from OrgRoster and make the PK (OrgID, RosterYear)
    OK, I had originally followed your advice on this but now I'm not so sure. The reason why I had an id in that table was so that I could reference it on the affiliation table - which includes a PersonID and an OrgRosterID. A person can belong to a specific Org, but as often happens he was only a member of said org on a particular year. That's what the OrgRoster table is all about. For instance, maybe the guy was a member of the CFR in '78-80 and then again in '95-2000 - do you see what I mean?

    I have rosters of these elite orgs, for specific years, which list the members for that year only.

    This stuff is in word docs or pdfs, and the amount of redundancy is incredible; it is cumbersome and not particular helpful to keep them in this format. That's why the data needs to be entered into a database once and for all; and as the years progress, and new rosters for that year are acquired, new members can be added as we go along.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, i guess i don't understand the point of the OrgRoster table then. it seems to me you could add a year column to the affiliation table and remove the OrgRoster table altogether.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are probably levels and levels above my knowledge of databases, but the redundancy of it is the reason. We're talking thousands and thousands of people, and each of them is tied to not one org but in most cases many orgs. Then, on top of that, each person is affiliated with a certain roster year. And, maybe they get elected to office. Traditionally they renounce their membership in these super-elite orgs, in order to appease the public - but just for their terms. Once they're private citizens again, it's right back in the game.

    How would you do it then? Before I start entering in thousands of names and affiliations, the schema needs to be right.

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ok, from the beginning, just to make sure i haven't missed anything:

    • you have people.
    • you have organizations.
    • people can be members of multiple organizations for a specified year.


    if that's it, then i only see three tables:
    Code mysql:
    create table person {
      id int auto_increment unsigned primary key
    , fname varchar(100)
    , ...
    , birth year
    , death year
    , bio text
    };
     
    create table org {
       id int auto_increment primary key
     , name varchar(100)
     , ...
    };
     
    create table org_person {
       person_id int unsigned
     , org_id int unsigned
     , membership_year year
     , primary key (persion_id, org_id, membership_year)
    };
    i don't understand why you had the extra layer of a roster in there? can one roster be unequivocally shared between two organizations?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  8. #8
    SitePoint Addict xDev's Avatar
    Join Date
    Jul 2003
    Location
    Moncton, New Brunswick, Canada
    Posts
    247
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you! It makes sense to me! I don't know why I was trying to make it more complicated than it was.

    Like I said, I'm pretty rusty when it comes to databases.


Bookmarks

Posting Permissions

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