Re: A general questio about names

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 Received on Fri Sep 09 2005 - 14:42:46 CEST