A general questio about names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John G. Eggert - 03 Sep 2005 19:40 GMT

I'm in the process of attempting to lay out a general overview of what a
properly set up database would look like for my current employer. My question
is with respect to names.

I seem to see at least 3 types of proper names that are independant
and have different issues. Employees, who have employee numbers and a SIN
(SSN for our American friends and apologies to the rest of the world). The
SIN seems like the appropriate primary key. Some employees leave and then
return and may have a different employee number. The second name is for
vendors who would require an artificial key. I can't imagine asking someone
selling me stuff to give me a SIN because I can't design a database without
one. This seems to imply that I need at least three separate tables. The
first for employee names, second for vendors, third for prospective
employees (no SIN yet, but if they become employees, it will become known,
etc. Prospective employees have no obligation and employers no right to
demand the SIN. I suspect though haven't checked that it is an offence to
demand the SIN except under very specific conditions. Application for
employment is NOT one of them)

Alternately, I can set up a single table for 'name'. In this case I would
need an artificial key, such as name#. The key would then be generated by some
sub program that assures it is unique. I notice that this is the route taken
by Date, though whether this is authoritative is beyond me. A second table
would be a join to 'name' with the SSN for a primary key and employee number
as a new value, a third etc. etc.

I don't know if it is rational to go to the extreme of a single table with
'firstname' a second table 'initial' a third table 'lastname' and then joins
to create proper names, one join for employees, one for vendors etc.

Any comments on the best (relational) method? I'm not looking for SQL code
here or a proper schema etc., but rather comments on perceived best practice.

Cheers.

JE

Reply to this Message

Mike - 09 Sep 2005 13:05 GMT

[snip]

>I seem to see at least 3 types of proper names that are independant
>and have different issues.

[snip]

Three /types/ of names? Hmmm.

Looks to me like three different kinds of relationships between
parties, not three types of proper names. "Employee" and "prospective
employee" describe two of several kinds of relationships between a
person and a company. "Vendor" describes one of several kinds of
relationships between two companies. But I could be wrong.

Signature

Mike Sherrill

Reply to this Message

dawn - 09 Sep 2005 13:42 GMT

> I'm in the process of attempting to lay out a general overview of what a
> properly set up database would look like for my current employer. My question
[quoted text clipped - 19 lines]
> sub program that assures it is unique. I notice that this is the route taken
> by Date, though whether this is authoritative is beyond me.

The "pattern" of using a Party with a generated id and subclasses of
Person and Organization has been written up in many places IIRC. A
table named Name would be a shame IMO. What is your entity? You have
a Party. You do not have a candidate key among the attributes for your
parties, so you generate an ID. Your party has a name, although
because a person's name typically has different attributes than an
organization's name, putting name attributes in the subclasses might
make sense.

> A second table
> would be a join to 'name' with the SSN for a primary key and employee number
> as a new value, a third etc. etc.

Your gen'd id would be the key to your Person & Org tables, but the
SIN/SSN could be another attribute in the Person table.

> I don't know if it is rational to go to the extreme of a single table with
> 'firstname' a second table 'initial' a third table 'lastname' and then joins
> to create proper names, one join for employees, one for vendors etc.

These names sound like attributes and not entities. Start your
modeling by looking at the actual entities. Look for nouns who objects
exist even if other objects are removed. A person exists as an entity,
while a name is an attribute of a person, for example. HTH --dawn

Reply to this Message

John G. Eggert - 10 Sep 2005 18:05 GMT

>> I'm in the process of attempting to lay out a general overview of what a
>> properly set up database would look like for my current employer. My question
[quoted text clipped - 44 lines]
> exist even if other objects are removed. A person exists as an entity,
> while a name is an attribute of a person, for example. HTH --dawn

Thanks Dawn:

Indeed, upon further work it is now apparent that these are indeed
attributes of different and independant entities. As such, they are not
something that can be moved from one entity to another. Even the move from
prospective employee to employee is not something where the attributes that
make up the name are necessarily transferrable. What I write on my resume
may not be the same as what my employer demands (no middle initial for
instance).

On a somewhat independant note, my current employee benefits package
requires that I give various people my SIN. It is my ID number for the plan.
So now the cable television guy at the hospital, the dentist's receptionist,
the optometrist and his teenage son, all have my SIN. This kind of insanity
is what happens when someone designs a database with the SIN or SSN as a
primary key. Please Please Please can everyone reading this group start
thinking a bit about the consequences of outstanding experts (Joe Celko
comes to mind) recommending the SSN or SIN be used as ANY type of key?

Cheers.

JE

Reply to this Message

-CELKO- - 10 Sep 2005 14:52 GMT

Vendors should have a verifiable tax id or Duns numbers. You want to
have an extrnal, verifiable id like the SIN -- and the law probalby
wants to you have it too :0

Do not do a table of names; a name is an attribute and not an entity.

Reply to this Message

John G. Eggert - 10 Sep 2005 21:34 GMT

> Vendors should have a verifiable tax id or Duns numbers. You want to
> have an extrnal, verifiable id like the SIN -- and the law probalby
> wants to you have it too :0
>
> Do not do a table of names; a name is an attribute and not an entity.

Joe:

Thanks for the reply. I was a bit zonked out from work when I wrote the
post. Indeed, a name is an attribute. It seems that some people advocate
normalizing to the point where one would have an entity called first_name or
some such and use this to generate queries of full names that would then be
attributes of an entity such as employee. I view this as going overboard,
but I'm not as well versed in the field as some.

As for SSN (or SIN here in Canada), my wife's pregnancy is requireing a lot of
use of my employee benefits package. I've had to give my SIN (the
'identification number' of the policy) to a number of people of questionable
integrity. I suspect this is because the database uses my SIN as the primary
key. This is a very bad thing. I'm not sure about the states, but in Canada
there is legislation detailing who is entitled to know one's SIN. If they
aren't entitled, the structure of the database shouldn't require it.
(Canadian law explicitly allows one to refuse to provide a SIN unless it can
be proved that it is required to comply with one of 18 specific laws and
regulations or 7 specific programs). Any primary key would be needed for
queries on the entity and hence made widely available. This leads me to the
point of view that the SIN or SSN should NEVER be used as a primary key.
Indeed, one should always question whether it is even needed in the context
of the database. If it isn't needed, don't include it. If it is needed, make
it 'hideable' so to speak.

Cheers.

JE

Reply to this Message

Christopher Browne - 11 Sep 2005 04:02 GMT

>> Vendors should have a verifiable tax id or Duns numbers. You want
>> to have an extrnal, verifiable id like the SIN -- and the law
[quoted text clipped - 27 lines]
> database. If it isn't needed, don't include it. If it is needed,
> make it 'hideable' so to speak.

This is indeed a serious problem.

People are now discovering just how horrible a thing it is to use
singular sorts of "universal identifiers" like SIN and SSN numbers.

There is certainly merit to having systematic ways to create
identifiers that don't fall prey to common problems that people have
too often fallen into.

GUIDs aka UUIDs also have both merits and demerits...

Signature

(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.

Reply to this Message

Dan - 12 Sep 2005 15:58 GMT

>>Vendors should have a verifiable tax id or Duns numbers. You want to
>>have an extrnal, verifiable id like the SIN -- and the law probalby
[quoted text clipped - 30 lines]
>
> JE

Calling Big Brother...

Reply to this Message

vldm10 - 15 Sep 2005 23:02 GMT

> > Vendors should have a verifiable tax id or Duns numbers. You want to
> > have an extrnal, verifiable id like the SIN -- and the law probalby
[quoted text clipped - 30 lines]
>
> JE

Whenever you need to hide the values of a primary key you can add a new
column in your table which should at least satisfy the definition of
the primary key.
Also, you need a good "device" for mass production of unique names.
This adding of a "surrogate key" can solve some other more complex
problems than hiding information.
I think your quetion is a very good one because nowadays there is more
and more usage of personal information by companies (supermarkets,
travel, medical, credit card companies, memberships, online
puchasing...)

Vladimir Odrljin

Reply to this Message