#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2002
    Posts
    4
    Rep Power
    0

    Need some DB experts: setting up a MySQL Database for a Curriculum Vitae system...


    I work for a department in a University that is looking to update the method which we currently "post" our CVs (Curriculum Vitae; similar to a resume) online. Currently, our professors update their CVs manually, send them to their secretaries, who in turn send them to me. I upload them and add them to our webpage manually.

    However, since I've taken over as webmaster, I've been making the push for more dynamic, database-driven sites. We're going to be switching to a PostNuke site soon, so people can add news, etc., on their own, without me having to intervene all over the place. This push for interactive sites has gone to the CV system. So, with that in mind, I'm looking to either program, or help implement, a new CV system that allows professors to directly publish their CVs via our site.

    Before I begin on programming this, though, I'm looking for the best way to do it, specifically in developing the database. I'm fairly new to MySQL databases, which is what we are using, so any help here would be appreciated.

    Here is what the system will do:

    -----

    Data is stored in a very granular, detailed style, then is dynamically formatted and delivered via the user's preferred method (PDF, LaTex, HTML, etc). Here is an example of the data:

    Professor Jones

    Positions:

    -Lecturer, Department of Biostatistics, UCLA, Los Angeles, California, 1975
    -Research Assistant Professor, Department of Medicine, University of Washington, Seattle, Washington, 1980-84

    -----

    As you can see, the "Position" category - which is one of several categories within the CV itself - is broken down like this:

    [Position]
    [Department Name]
    [School Name]
    [University Name]
    [City]
    [State]
    [Dates Position Held]

    As you can see, this is going to be a fairly intricate and complex system. At the moment, I can't figure out the best way to develop it. I hope what I've written isn't too confusing; here is another example to help you understand.

    Professor Jones

    -Educational Background
    • -School
      -City, State
      -Degree
      -Year

    -Positions
    • -subcategories also shown above

    -Honors and Awards
    • -Award
      -Year

    -Professional Activities
    • -Activity
      -Year


    That's the gist of it, but the fields continue after that. The online CVs we will be providing will also have published articles, which would be separated into several subcategories (author(s), title, date, journal name), books, teaching experience, and a number of other categories, each which will have subcategories.

    I'm somewhat familiar w/MySQL, as I stated, but this is above my head. Can anyone lend a hand or point me in the right direction? I have some PHP/MySQL books at my side and all summer to program this system, but I can't quite get a grasp on it yet.

    a billion (and one) thanks,
    Rob
    Last edited by Entity; June 24th, 2002 at 02:43 PM.
  2. #2
  3. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    From what I can tell, you have basically layed out what you want for your table designs. They all have one field in common for a foreign key. So you could easily do something like this for table structures:
    Code:
    create database CurriculumVitae:
    create table education (
        reference_no int not null,
        userid varchar(50) not null,
        school varchar(75),
        city varchar(35), 
        state char(2),
        degree char(5),
        year int,
        primary key(reference_no,userid)
    );
    create table position (
        reference_no int not null,
        userid varchar(50) not null,
        department varchar(25),
        school varchar(75),
        university varchar(75),
        city varchar(35),
        state char(2),
        date_from date,
        date_to date,
        primary key(reference_no,userid)
    );
    create table honors (
        reference_no int not null,
        userid varchar(50) not null,
        award varchar(50),
        year int,
        primary key(reference_no,userid)
    );
    create table prof_activities (
        reference_no int not null,
        userid varchar(50) not null,
        activity varchar(50),
        year int,
        primary key(reference_no,userid)
    );
    The reason why I layed it out with two primary keys for each table is that so you can have multiple entries for each user and the primary key remains unique for each user. Userid should correspond to the table in which you keep faculty information in. Then you have a common link between all different tables to reference against. It would be easy enough to add extra tables and keep inside of the database layout.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Remember to think of 'entities' and model those out.

    I'd have a 'CV' table which stores stuff like first/last name, address, etc. -- rather than having 2 primary keys you can simply make them foreign keys to the CV table which already stores the user info.

IMN logo majestic logo threadwatch logo seochat tools logo