views:

44

answers:

2

I am new to SQL Server 2008 database development.

Here I have a master table named ‘Student’ and a child table named ‘Address’. The common column between these tables is ‘Student ID’.

My doubts are:

  1. Do we need to put ‘Address Id’ in the ‘Address’ table and make it primary key? Is it mandatory? ( I won’t be using this ‘Address Id’ in any of my reports )

  2. Is Primary key column a must in any table?

Would you please help me on these.

Would you please also refer best links/tutorials for SQL Server 2008 database design practices (If you are aware of) which includes naming conventions, best practices, SQL optimizations etc. etc.

A: 

Every table should have a way to uniquely and unambiguously identify a record. Make AddressID the primary key for the address table.

Without a primary key, the database will allow duplicate records; possibly creating join problems or trigger problems (if you implement them) down the road.

RichO
+3  A: 

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

OMG Ponies
I don't think that sharing addresses between students is generally good idea. You usually edit address data inline with other student's basic data, so You can end up with changing someone else's address. Your constructions doesn't take into consideration fact that You usually need current address and going through another table and looking for rows with current date between effective_date and expiry_date can slow everything down.
LukLed
@LukLed: You would recommend duplicated, redundant data for the concern of a users typo? You'll have a hard time convincing a DBA that is a good idea. People will move, but addresses will always be for the most part static. What about an address that lacks a postal code, or has an incorrect spelling - stored in a single entry, it's corrected once. Summarily, my setup saves users time because they can reference existing addresses when they exist, and this minimizes the likelihood of an incorrect and/or incomplete address.
OMG Ponies
@LukLed: Finding the current address is trivial. After all, you'd only be looking for one specific entry, and we're talking about a JOIN (on indexes) of all things.
OMG Ponies