RDBMS Key Confusion

Question

I'm trying to sort out the database behind my company's website.

We deal with both Sites and Residents. At the moment the Site Reference is included in the Residents table as a foreign key but, obviously, this is only in 1NF.

Site references are of the form nnnnn (12345, for example). Each resident has their own reference within the Site of the form nnn (starting at 001 and ending at a maximal 999).

The problem is, when I take it to 2NF:

Site(Reference, Name, Charge)

Residency(SiteReference, ResidentReference,)

Resident(Reference, Address, Username, Password)

The Resident Reference will never uniquely identify one Resident as, if there are 2 sites, then there will be 2 001s. I can't use username as this field is only populated once (and if!) they have registered for our web service. I'd simply introduce a new autonumber ID for Residents but there's something niggling at me that there is a more elegant solution. Can anyone suggest what it is?

Answer 1

You need to "free" the resident reference from the site - e.g. make it a ResidentID and make sure each ResidentID is globally unique within the Resident table. Every Site must also have a globally unique SiteID within the Site table.

Then and only then will you be able to create a good db schema.

The Residency table will provide the link between residents (identified uniquely by their ResidentID) and the sites (again - uniquely identified by their SiteID).

Marc