Re: Help finding natural keys

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Fri, 17 Jan 2003 19:39:22 GMT
Message-ID: <KZYV9.71$I55.9126_at_news2.east.cox.net>

--CELKO-- wrote:

Mr Celko

Thank you for taking the time to respond to my post. I appreciate your insight. I am grateful for all the input I've received so far at c.d.theory. It is most helpful.

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

I have SFS. I'll get D&D then. If it is more about modeling, it will be a help. It sounds like there's more to it than sequences versus SELECT MAX().

(I'll be sure to follow the formatting standards that people on this group expect in the future. Thank you for fixing my formatting.)

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

I will never permit myself to suffer the indignity of being fingerprinted by my grocer.

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

I offered up the Tax ID myself in another post. It is a candidate key for the United States. I am sure other countries have a similar code. So maybe country and tax id number.

Are there any countries out that don't collect taxes?

>>> CREATE TABLE Patients


>  (firm_id INTEGER NOT NULL


>          REFERENCES Firms(firm_id),


    last_name VARCHAR(32) NOT NULL,
    ssn VARCHAR(32),

    sex CHAR(1),
    ...
> 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. <<

I omitted what I thought were obvious columns from the DDL. My apologies. I'll not do it again in future posts.

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

SSN, of course, is a candidate key, but I don't see people using it as a primary key often, so I assume their are issues. One that occurs to me is if a patient is admitted in an emergency and their SSN cannot be determined at the time of admission. (We do not deal with emergency just yet.)

Also, with SSN, there would be a problem since firms are not supposed to know about each others patients. If I were to use SSN as a key, then I would have duplicates when I insert duplicates. I have to pretend that each firm is in its own database. Maybe a combination of firm_id and SSN?

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

Indeed, the reality is somewhat unorganized, but they get by. Most of our customers are looking for us to replace paper patient charts.

My point here is that they don't already have a serial number system in place for patients in many organizations. This is a charting application, not a billing application. In the real world, patients are identified by name by someone who is responsible for a dozen or so patients.

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

There are no serial numbers yet. These heath care organizations use questionnaires designed by the nurses, usually in Word. They are printed out, photocopied, and tucked to patient dossiers.

The questionnaire is a model of a blank questionnaire, it is meta data, a completed questionnaire is an instance of the questionnaire. A health care worker can complete a particular questionnaire more than once for a patient.

This is part of a larger schema, and I didn't want to be rude with a huge post. A questionnaire contains asked_questions, with related offered_responses, chosen from a pool of questions with related responses. This is because a question can be asked on more than one questionnaire.

(Actually, a questionnaire can have sub-questionnaires, and so it becomes a parts explosion just like the menu problem of mid-December. I've been following the tree discussions. Eagerly awaiting the new tree book.)

This brings it together with patient:

create table patient_completed_questionnaire (

    firm_id integer not null,
    patient_id integer not null,
    questionnaire_id integer not null,
    completed_questionnaire_id integer not null,     primary key (firm_id, patient_id,

                 questionnaire_id, completed_questionnaire_id),

    foreign key (firm_id, patient_id) references patient,     foreign key (firm_id, questionnaire_id, completed_questionnaire_id)

                references completed_questionnaire

);

I kept the patient id out of questionnaire since we might want to complete questionnaires related to something other than patients.

Alan Gutierrez - ajglist_at_izzy.net Received on Fri Jan 17 2003 - 20:39:22 CET