Re: A general questio about names

On 2005-09-09, dawn <dawnwolthuis_at_gmail.com> wrote:
>
> John G. Eggert wrote:
>> 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.
>
> 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
>

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 Received on Sat Sep 10 2005 - 19:05:52 CEST