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]

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]

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]

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

 

[John Salerno]

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]

John Salerno a écrit :

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]

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]

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.

 

[Dennis Lee Bieber]

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?

I've developed the habit of specifying an auto-increment ID on
practically every table I work with -- even intersection tables in which
the two foreign keys together are unique. (Among other things, it does
allow for sorting data in the order of data entry).

For the table in question, you'd have a primary key, and a foreign
key reference...

ID auto increment primary key
studentID integer foreign key references Student.ID
whatever...

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Salerno]

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

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]

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

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

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

[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

 

[Dennis Lee Bieber]

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?

The "person ID" is only a primary key for the "person record"... You
can use it to link to the person in as many tables/records as needed.
Each of those other tables, though, should have something that is a
primary key for them.

Some text books even recommend that the primary key be totally
useless outside the database (so, no hashing of last name and date of
birth, etc. -- it should just be a database assigned number, used only
within the database, but not for external reports).

Recommend you look for some textbook (or do a Google search for a
tutorial) that discusses: Codd Normal Forms, Relational Database Design,
Normalization.

Let's start with a very unnormalized record (I won't bother with
data types, and some "fields" actually have more subfields inside --
I'll cover those in later phases):

StudentRecord(sID, sName, sDOB, sSchool_1, sSchool_2, sSchool_3)

(where sSchool_x is really a collection of
[name, start, end, degree, major] )

" 123, Great Pumpkin, 1955, (hard knocks, 1956, 1960, BS, Terror),
(Petaluma Agricultural, 1960, 1962, MS, Gourds), (empty) "

This is a bad design... If a student doesn't have any school data,
you are storing empty fields. OTOH, if a student has more than three
schools, you are unable to store all of them. First refactoring to solve
this...

StudentRecord(pKey, sID, sName, sDOB, sSchool)

"1, 123, Great Pumpkin, 1955, (hard knocks, 1956, 1960, BS, Terror)"
"2, 123, Great Pumpkin, 1955, (Petaluma Agricultural, 1960, 1962, MS,
Gourds) "

Better, we can just add another record for each school. Bad: we are
repeating the student ID, student name, DoB for each school (note the
new primary key) -- it is possible to get conflicting data (what happens
if someone updates the second record and changes the DoB to 1559?)

We must split the table!

StudentRecord(sID, sName, sDOB)

" 123, Great Pumpkin, 1955 "

EducationRecord(eID, sID, eName, eStart, eEnd, eDegree, eMajor)

" 89, 123, hard knocks, 1956, 1960, BS, Terror "
"255, 123, Petaluma Agricultural, 1960, 1962, MS, Gourds "

Note how each education record has its own primary key (eID), AND
has the foreign key identifying the student.

To retrieve the combined data:

select sID, sName, sDOB, eName, eStart, eEnd, eDegree, eMajor from
StudentRecord
left join EducationRecord
on StudentRecord.sID = EducationRecord.sID
order by StudentRecord.sID, eID;

(Note: the "order by" presumes the education records were entered in
chronological order and that the primary key was an "auto-increment"
type, so newer data has higher values... A more formal "order by" would
specify perhaps the eStart date)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/