|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
#1
June 24th, 2002, 02:41 PM
|
|||
|
|||
|
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
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
June 24th, 2002, 04:25 PM
|
||||
|
||||
|
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)
);
|
|
#3
June 25th, 2002, 07:21 AM
|
|||
|
|||
|
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.
__________________
Matt - matt@fanhome.com FanHome.com - Where Sports Fans Connect (our SYBASE-backed vB Forums!) Sybase DBA / PHP fanatic  Sybase v. MySQL v. Oracle | Why I don't like MySQL  | Download Sybase TODAY! | Visit DBForums.com for all your RDBMS talk!
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Need some DB experts: setting up a MySQL Database for a Curriculum Vitae system... |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
