proper format for this database table

[John Salerno]

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.

[Gabriel Genellina]

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.
--
Gabriel Genellina
Softlab SRL

__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas

[Bruno Desthuilliers]

John Salerno a écrit :

Hi guys. I was wondering if someone could suggest some possible
structures for an "Education" table in a database.

Wrong newsgroup, then. comp.database.* is right next door...

[mensanator]

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

[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

[John Salerno]

me********@aol.com wrote:

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

Thanks guys. I do plan to have an id entry for each person as well, I
just forgot to mention that. But since it's a primary key, I didn't know
I could list it more than once. Or does primary key not necessarily mean
unique?

[John Salerno]

Bruno Desthuilliers wrote:

John Salerno a écrit :

>Hi guys. I was wondering if someone could suggest some possible
structures for an "Education" table in a database.

Wrong newsgroup, then. comp.database.* is right next door...

I know, I'm sorry. It's just that this newsgroup server doesn't have any
database ngs on it. :(

[Carsten Haese]

On Fri, 2006-10-20 at 10:57, John Salerno wrote:

me********@aol.com wrote:

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

Thanks guys. I do plan to have an id entry for each person as well, I
just forgot to mention that. But since it's a primary key, I didn't know
I could list it more than once. Or does primary key not necessarily mean
unique?

Primary key *does* mean unique in the table that defines it. However, if
you take a primary key ID from one table and store it in a different
table, that's a foreign key. There are no inherent uniqueness
constraints on a foreign key.

-Carsten

[Neil Cerutti]

On 2006-10-20, John Salerno <jo******@NOSPAMgmail.comwrote:

Bruno Desthuilliers wrote:

>John Salerno a écrit :

>>Hi guys. I was wondering if someone could suggest some possible
structures for an "Education" table in a database.

Wrong newsgroup, then. comp.database.* is right next door...

I know, I'm sorry. It's just that this newsgroup server doesn't
have any database ngs on it. :(

Try Google Groups for these annoying cases.

--
Neil Cerutti
The audience is asked to remain seated until the end of the recession.
--Church Bulletin Blooper

[John Salerno]

Carsten Haese wrote:

>>[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

Thanks guys. I do plan to have an id entry for each person as well, I
just forgot to mention that. But since it's a primary key, I didn't know
I could list it more than once. Or does primary key not necessarily mean
unique?

Primary key *does* mean unique in the table that defines it. However, if
you take a primary key ID from one table and store it in a different
table, that's a foreign key. There are no inherent uniqueness
constraints on a foreign key.

So in the example above, isn't that using the same primary key multiple
times in the same table?

[John Salerno]

Neil Cerutti wrote:

On 2006-10-20, John Salerno <jo******@NOSPAMgmail.comwrote:

>Bruno Desthuilliers wrote:

>>John Salerno a écrit :
Hi guys. I was wondering if someone could suggest some possible
structures for an "Education" table in a database.
Wrong newsgroup, then. comp.database.* is right next door...

I know, I'm sorry. It's just that this newsgroup server doesn't
have any database ngs on it. :(

Try Google Groups for these annoying cases.

True, didn't think of that. (Probably a good idea to make up a fake
account though, since GG posts your email address.)

[mensanator]

John Salerno wrote:

Carsten Haese wrote:

>[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

Thanks guys. I do plan to have an id entry for each person as well, I
just forgot to mention that. But since it's a primary key, I didn't know
I could list it more than once. Or does primary key not necessarily mean
unique?

Primary key *does* mean unique in the table that defines it. However, if
you take a primary key ID from one table and store it in a different
table, that's a foreign key. There are no inherent uniqueness
constraints on a foreign key.

So in the example above, isn't that using the same primary key multiple
times in the same table?

Actually, the [id] in the example was intended to be the
foreign key, I didn't specify an id for the degree records
themselves. A more typical example would be

[Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
5 1 U of I 1971 1975 BS
6 1 U of I 1975 1976 MS
7 1 U of I 1976 1977 PhD

where [Eid] is the primary key of the Education table and
[Sid] is the foreign key from the Student table so that the
single student record (1) links to three education records
(5,6,7).

[John Salerno]

me********@aol.com wrote:

[Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
5 1 U of I 1971 1975 BS
6 1 U of I 1975 1976 MS
7 1 U of I 1976 1977 PhD

where [Eid] is the primary key of the Education table and
[Sid] is the foreign key from the Student table so that the
single student record (1) links to three education records
(5,6,7).

Hmmm. I think I'm lost. My table will have a key that represents the
persons ID, in my case it would be salerjo01. This will be what ties
this table to that person. Can I repeat this key multiple times in the
Education table?

[mensanator]

John Salerno wrote:

me********@aol.com wrote:

[Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
5 1 U of I 1971 1975 BS
6 1 U of I 1975 1976 MS
7 1 U of I 1976 1977 PhD

where [Eid] is the primary key of the Education table and
[Sid] is the foreign key from the Student table so that the
single student record (1) links to three education records
(5,6,7).

Hmmm. I think I'm lost. My table will have a key that represents the
persons ID, in my case it would be salerjo01. This will be what ties
this table to that person. Can I repeat this key multiple times in the
Education table?

Yes, because it would be a foreign key.

Perhaps a simple example might help:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

# in the [person] table, [personid] is the primary key
# but in the [education] table, it's a foreign key
# I deliberately didn't make them AUTOINCREMENT to
# simplify the example
cur.executescript("""
create table person(
personid PRIMARY KEY,
firstname,
lastname
);

create table education(
educationid PRIMARY KEY,
personid,
institution,
yearStart,
yearEnd,
degreeEarned
);
""")

persons = [(1,'Tom','Smith'), \
(2,'Dick','Smith'), \
(3,'Harry','Smith')]

degrees = [(1,1,'University of Illinois',1971,1975,'BS'), \
(2,1,'University of Illinois',1975,1976,'MS'), \
(3,1,'University of Illinois',1976,1977,'PhD'), \
(4,2,'University of Illinois',1971,1974,None), \
(5,2,'DeVry Institute of Technology',1974,1976,'ASEET')]

cur.executemany("""
INSERT INTO person(personid,
firstname,
lastname)
VALUES (?,?,?)"""
, persons)

cur.executemany("""
INSERT INTO education(educationid,
personid,
institution,
yearStart,
yearEnd,
degreeEarned)
VALUES (?,?,?,?,?,?)"""
, degrees)
# Note: since both tables have a field named [personid],
# the table name must be included when referencing
# that field
cur.execute("""
SELECT firstname,
lastname,
institution,
yearStart,
yearEnd,
degreeEarned
FROM education
INNER JOIN person
ON person.personid = education.personid
ORDER BY education.personid;
""")

report = cur.fetchall()
for i in report:
print '%5s %-5s %-30s %d-%d %-6s' % (i)

## Tom Smith University of Illinois 1971-1975 BS
## Tom Smith University of Illinois 1975-1976 MS
## Tom Smith University of Illinois 1976-1977 PhD
## Dick Smith University of Illinois 1971-1974 None
## Dick Smith DeVry Institute of Technology 1974-1976 ASEET

## Note that the third person, Harry Smith, doesn't show up
## in the report.
## That's because no education records were created for him.
## When you do an INNER JOIN between two tables, the linking field,
## [personid] must exist in both tables.

[mensanator]

me********@aol.com wrote:

John Salerno wrote:

me********@aol.com wrote:

[Eid] [Sid] [university] [yearStart] [yearEnd] [degreeEarned]
5 1 U of I 1971 1975 BS
6 1 U of I 1975 1976 MS
7 1 U of I 1976 1977 PhD
>
where [Eid] is the primary key of the Education table and
[Sid] is the foreign key from the Student table so that the
single student record (1) links to three education records
(5,6,7).
>

Hmmm. I think I'm lost. My table will have a key that represents the
persons ID, in my case it would be salerjo01. This will be what ties
this table to that person. Can I repeat this key multiple times in the
Education table?

Yes, because it would be a foreign key.

Perhaps a simple example might help:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

# in the [person] table, [personid] is the primary key
# but in the [education] table, it's a foreign key
# I deliberately didn't make them AUTOINCREMENT to
# simplify the example
cur.executescript("""
create table person(
personid PRIMARY KEY,
firstname,
lastname
);

create table education(
educationid PRIMARY KEY,
personid,
institution,
yearStart,
yearEnd,
degreeEarned
);
""")

persons = [(1,'Tom','Smith'), \
(2,'Dick','Smith'), \
(3,'Harry','Smith')]

degrees = [(1,1,'University of Illinois',1971,1975,'BS'), \
(2,1,'University of Illinois',1975,1976,'MS'), \
(3,1,'University of Illinois',1976,1977,'PhD'), \
(4,2,'University of Illinois',1971,1974,None), \
(5,2,'DeVry Institute of Technology',1974,1976,'ASEET')]

cur.executemany("""
INSERT INTO person(personid,
firstname,
lastname)
VALUES (?,?,?)"""
, persons)

cur.executemany("""
INSERT INTO education(educationid,
personid,
institution,
yearStart,
yearEnd,
degreeEarned)
VALUES (?,?,?,?,?,?)"""
, degrees)
# Note: since both tables have a field named [personid],
# the table name must be included when referencing
# that field
cur.execute("""
SELECT firstname,
lastname,
institution,
yearStart,
yearEnd,
degreeEarned
FROM education
INNER JOIN person
ON person.personid = education.personid
ORDER BY education.personid;
""")

report = cur.fetchall()
for i in report:
print '%5s %-5s %-30s %d-%d %-6s' % (i)

## Tom Smith University of Illinois 1971-1975 BS
## Tom Smith University of Illinois 1975-1976 MS
## Tom Smith University of Illinois 1976-1977 PhD
## Dick Smith University of Illinois 1971-1974 None
## Dick Smith DeVry Institute of Technology 1974-1976 ASEET

## Note that the third person, Harry Smith, doesn't show up
## in the report.
## That's because no education records were created for him.
## When you do an INNER JOIN between two tables, the linking field,
## [personid] must exist in both tables.

If you actually DO want to see Harry Smith listed in the
report even though he has no education, you would change
the INNER JOIN to a RIGHT JOIN. But sqlite3 doesn't
support RIGHT JOIN, only LEFT JOIN. So we just have
to change the direction of the JOIN. Instead of from
education to person, make it it from person to
education and then we can use LEFT JOIN. And it might
be a good idea to sort on person.personid since
education.personid will be null if there is no matching
record.

The modified query becomes:

cur.execute("""
SELECT firstname,
lastname,
institution,
yearStart,
yearEnd,
degreeEarned
FROM person
LEFT JOIN education
ON person.personid = education.personid
ORDER BY person.personid;
""")

And I had to change the print statement since nulls
(which translate to None) crash the %d. With that
change we now can see ALL the people and note
that Harry is uneducated.

Tom Smith University of Illinois 1971-1975 BS
Tom Smith University of Illinois 1975-1976 MS
Tom Smith University of Illinois 1976-1977 PhD
Dick Smith University of Illinois 1971-1974 None
Dick Smith DeVry Institute of Technology 1974-1976 ASEET
Harry Smith None None-None None