Re: Help finding natural keys

>> I am generating keys using select max. I've considered what Mr
Celko says about how this is just a sequential file model in SQL, but I try as I might,
I can't think of good, er, natural keys for my entities. <<

Sometimes you do have to create your own codes. I have a lot to say about how to design encoding schemes in my book DATA & DATABASES -- check digits, syntax, a taxonomy of encoding schemes, scales and measurement theory, etc.

>> Have I just fallen into a bad habit? Are there candidate keys for
things
like a person that I just cannot see? <<

Kroger's grocery store chain is now using fingerprints if you want to cash a check at one of their stores. That is a very solid candidate key!

>> CREATE TABLE Firms

  (firm_id INTEGER NOT NULL PRIMARY KEY,    firm_name VARCHAR(32) NOT NULL);

 The firm may change it's name, so that doesn't make for a good key. <<

I'll bet they don't change their tax id number very often and you need that number anyway for reporting to the Internal Revenue Service.

>> CREATE TABLE Patients

 (firm_id INTEGER NOT NULL

         REFERENCES Firms(firm_id),
  patient_id INTEGER NOT NULL
  PRIMARY KEY (firm_id, patient_id));

A patient is a charge of a particular hospital so, I select the max patient
id for the hospital to get a new patient id. <<

Bet that messes up the data on the re-admits. No SSN? No insurance polocy numbers? Look for a key that can be verified extrernally.

>> CREATE TABLE Questionnaires

  (firm_id INTEGER NOT NULL

         REFERENCES Firms(firm_id),
   questionnaire_id INTEGER NOT NULL
   PRIMARY KEY (firm_id, questionnaire_id));

... There is no unique identifier for these things in the real world where
they are photocopied Word documents designed by a nurse. <<

Nobody can help you if the reality you are trying to model is screwed up.

>>

CREATE TABLE Completed_Questionnaires
(firm_id INTEGER NOT NULL

         REFERENCES Firms(firm_id),
 questionnaire_id INTEGER NOT NULL,
 completed_questionnaire_id INTEGER NOT NULL,  PRIMARY KEY (firm_id, questionnaire_id, completed_questionnaire_id));  

 Here is an instance of a questionnaire. A questionnaire might be completed a
number of times during a patient's care, like a progress report form. Again, select MAX(). <<

Completion is an attribute of a questionnaire. This sounds more like an occurence of a questionnaire which has a date or serial number assoicated with it. Received on Fri Jan 17 2003 - 12:39:18 CST