1) Yes, having an ADDRESS_ID
column as the primary key of the ADDRESS
table is a good idea.
But having the STUDENT_ID
as a foreign key in the ADDRESS
table is not a good idea. This means that an address record can only be associated to one student. Students can have roommates, so they'd have identical addresses. Which comes back to why it's a good idea to have the ADDRESS_ID
column as a primary key, as it will indicate a unique address record.
Rather than have the STUDENT_ID
column in the ADDRESS
table, I'd have a corrollary/xref/lookup table between the STUDENT
and ADDRESS
tables:
STUDENT_ADDRESSES_XREF
STUDENT_ID
, pk, fk to STUDENTS
table
ADDRESS_ID
, pk, fk to ADDRESS
table
EFFECTIVE_DATE
, date, not null
EXPIRY_DATE
, date, not null
This uses a composite primary key, so that only one combination of the student & address exist. I added the dates in case there was a need to know when exactly, because someone could move back home/etc after all.
Most importantly, this works off the ADDRESS_ID
column to allow for a single address to be associated to multiple people.
2) Yes, defining a primary key is frankly a must for any table.
In most databases, the act also creates an index - making searching more efficient. That's on top of the usual things like making sure a record is a unique entry...