proper format for this database table

[mensanator at aol.com]

John Salerno wrote:
> mensanator at 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.