proper format for this database table

[mensanator at aol.com]

Gabriel Genellina wrote:
> At Thursday 19/10/2006 17:07, 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.
>
> The relationship should be 1:n between a Person and its Education records.
> Person 1<--->n Education
> so just add a PersonId to the table and you're done.
> Depending on your needs, later university and degreesEarned may
> become entities instead of simple text attributes.

And if multiple degrees are earned at the same university,
they should be in seperate records. So if you were thinking

[id] [university] [yearStart] [yearEnd] [degreesEarned]
1     U of I       1971        1977      BS MS PhD

you should rethink it as

[id] [university] [yearStart] [yearEnd] [degreeEarned]
1     U of I       1971        1975      BS
1     U of I       1975        1976      MS
1     U of I       1976        1977      PhD