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
--
http://mail.python.org/mailman/listinfo/python-list
