proper format for this database table

[Jordan Greenberg]

John Salerno wrote:
> Hi guys. I was wondering if someone could suggest some possible
> structures for an "Education" table in a database. It will contain at
> minimum university names, years of attendance, and degrees earned. My
> problem is that I don't know quite how to set this up for people who
> have attended multiple universities. I could do:
> 
> university text DEFAULT NULL,
> yearStart integer DEFAULT NULL,
> yearEnd integer DEFAULT NULL,
> degreesEarned text DEFAULT NULL
> 
> But this only allows for one university. (Also not sure if I should
> split up the years like that.) But mainly I'm looking for a way to
> construct this table so it can hold multiple universities, if necessary.
> 
> Thanks.

Use associative tables.
Something like:

Table Students:
	PK id (some unique id, maybe a student id#, or just an auto-inc)
	name, etc...

Table Students2Education:
	studentID (id from Students)
	EducationID (id from Education)

Table Education:
	id (probably just some auto-inc)
	university
	yearStart
	yearEnd
	degreesEarned

This way, if you have some students:

Students:
001 Jordan


and Jordan started university in 2003 @ Wentworth Institute of Technology:

Students2Education:
001 Wentworth_Institute_Of_Technology

Education:
1 Wentworth_Institute_Of_Technology 2003 NULL NULL

And then, in the future, say I go to MIT. By then I'll (hopefully) have
my CS degree...

Students:
001 Jordan

Students2Education:
001 Wentworth_Institute_Of_Technology

Education:
1 Wentworth_Institute_Of_Technology 2003 2007 BCOS
2 Massachusetts_Institute_Of_Technology 2008 NULL NULL

And I could go back to Wentworth and major in Computer Engineering this
time:

Education:
1 Wentworth_Institute_Of_Technology 2003 2007 BCOS
2 Wentworth_Institute_Of_Technology 2007 200

(You should probably use an integer ID for universities, and have a
separate table to link those to names. Something like:

Education:
UniversityID
yearStart
yearEnd
degreeEarned

Universities:
UniversityID
Name
City
Etc,etc)

In general, when you're having trouble representing something in a
database, it helps to break it down and model the smaller relationships
first, and use those as building blocks to model the whole relationship.

HTH.
Jordan Greenberg




-- 
Posted via a free Usenet account from http://www.teranews.com