Here is my two-pen-worth.

Long ago I helped design a schema for a Patient Information database for
the Health Service. We had much the same problems with people not having
all of their information and had to devise a way where we could hang all
of their hospital stay information on their record when we couldn't
uniquely identify them.

The answer was to generate a sequence number for each one - then it didn't
matter if some of the other details were missing. We used the sequence
number as the major key to all tables containing supplementary data.

In general, the information sent to the DOH was anonymised so we didn't
need to include any of the potentialy missing information - it was just
held on the database as a reference.

Nothing stops you doing a full table search on any column - they don't
have to be keys.

After all, if you have a 3 part unique key which can all be nullable, as
soon as you have one person with none of the information, that is unique
and no-one else with that lack can be added.

Theory is all very well, but in the real world, it's a practical solution
that everyone needs.

Thats all from me!

Lin



Internet
paul (AT) ipauland (DOT) com
Sent by: info-ingres-bounces (AT) kettleriver...ting (DOT) com
18/08/2011 12:11
Please respond to
info-ingres (AT) kettleriverconsulting (DOT) com


To
info-ingres (AT) kettleriverconsulting (DOT) com
cc

Subject
Re: [Info-Ingres] Invitation to a quarrel






On 18/08/2011 11:24, Roy Hann wrote:
Well what I have in mind is something like a vagrant hostel, or
government benefit agency. They come across people all the time who may
have unique forms of identification, but it can take multiple forms.
Because we started off talking about primary keys composed of unique
nullable columns I have avoided even mentioning creation of an
artificial key as a solution, because it's not relevant to the discussion.

For the purpose of running my government benefit system, claimants must
provide a name (non-unique), and one or more of three forms of unique ID
(Key1, Key2, Key3). Having a primary key made from three unique and
nullable keys( Key1, Key2 and Key3) is the natural solution.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres


__________________________________________________ _________
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is prohibited.

Please refer to http://www.bnpparibas.co.uk/en/infor...de=ECAS-845C5H for additional disclosures.

On 18/08/2011 12:59, lin.a.bradbrook (AT) uk (DOT) bnpparibas.com wrote:
Absolutely, we can't let Roy just get his own way all the time. I
thought Roy might pounce on the wastefulness when dealing with my
multiple column nullable key, when dealing with foreign keys in other
tables, and the implications when one of the previously null keys is
found and the foreign key in other tables no longer matches it.

Generally the creation of an artificial key (universally popular amongst
developers) solves some ills but creates others and puts Roy off his tea.

I've always wondered how people migrate data between databases when it's
held together by artifical keys that may not be unique between databases
or even auto generated by the DB engine itself.

Anyway, we are having fun.

Paul Andrews wrote:

I'm just back in the office and see my name being invoked, so I'll
quickly respond to this one!

You will evidently be surprised to hear that I am perfectly OK with
synthetic keys (artificial keys that are visible to the users) and
surrogate keys (artificial keys that invisible to the users).

I just ask that they not be used when there is already a reliable
(i.e. unique, stable, concise, familiar) natural key that works.

If there is no such natural key then by all means introduce an
artificial one.

As an aside, ALL keys are artificial keys at some level. My passport
number number is an artificial key as far as the issuing authority
is concerned; it is a natural key to my airline. Likewise my driver's
licence number is artificial to the licensing authority but natural to
the police. And so on.

PS: FOREIGN KEY (fk) REFERENCES table ON UPDATE CASCADE is our
friend... :-)

--
Roy

UK Ingres User Association Conference 2012 will be on Tuesday June 19 2012.
*NOTE THE CHANGED DATE* See www.uk-iua.org.uk

On Aug 18, 2011, at 9:58 AM, Roy Hann wrote:

I don't like anything about me being a natural key for the police ...

;-)

Paul Andrews writes

even auto generated by the DB engine itself.



In my experience we have chosen to stick to one identification scheme when
translating old to new data, maintaining a separate field which links back
to the old unique key. In the case of numeric member ids, we've usually
added a digit to the front or mapped it to a different number range. For
large data sets like financial transactions, we let the database generate
the unique code as it is migrated from one set to the other.



Accounting GL codes and product id codes are more problematic because real
people are involved. We have often modified the application to cope with
both old and new codes until business procedures and people can cope with
the new coding schemes.





Paul White

Trying to keep up down here, Friday night .



Paul Andrews writes

I don't think I have understood this correctly. The design implies, from a
logical point of view, we need a minimum of one key value to ensure
uniqueness. We can have just one entity with all key values set to null.
Separate indexes on each key would allow duplicates, right? ( to support
selections like WHERE key3 IS NULL). This contradicts the "three unique and
nullable keys". Regardless of whether we use NULLS, empty string or a
marker values, it smells wrong which makes me think I have misunderstood.




Anyway I am missing the mechanism which stops two people from having the
same social security number.





Roy writes


And a fourth table for the people, right? My preferred implementation
would be two tables. A table for the people: Key = unique (identifier) and
a table for optional facts: Key = unique (Fact Type, Fact Value). Either
case, four tables or two, ensures a unique fact like passport can be linked
to just one person. To enforce at least one fact is registered I've got
this silly design.



Person Table

person_id unique PK,

Fact1_id not null reference to Fact,

Fact2_id with null optional reference to Fact,

Fact3_id with null optional reference to Fact.



Fact table

fact_id unique PK,

Fact_type not null,

Fact_value not null,

Person_id not null reference to Person.

Unique Index fact_type, fact_value





ummm where is that bourbon?



Paul White

I suspect the confusion is a misunderstanding between the
logical->physical key design of a database and the business logic
requirements of the application. Null keys make no sense at all.
(Relational purists hate null completely). There is a reasonable
business rule requirement that at least one of three fields in a set
have a value but that should be unrelated to unique row identification.

In this case, it appears the designer would like to have the database
"unique index" feature directly support enforcing the business
requirement. This strikes me as a classic case of creating a cool
feature for a special case which will create all kinds of headaches for
the larger community.


--
daryl.monge (AT) ingres (DOT) com
------------------------------------------------------------------------
daryl.monge (AT) ingres (DOT) com's Profile: http://community.ingres.com/forum/member.php?userid=736
View this thread: http://community.ingres.com/forum/sh...ad.php?t=13820