Re: How do people usually solve this?

In article <2ghgba$cqm_at_meaddata.meaddata.com>, lonnie_at_meaddata.com (Lonnie Barnett) writes:
|> In article <2gg40m$i7e_at_canopus.cc.umanitoba.ca>, umyin_at_cc.umanitoba.ca (Qing Yin) writes:
|> |> I'm comparing several solutions of a common problem. Could netters kindly
|> |> contribute some comments, or let us know how your database deals with this
|> |> problem?
|> |>
|> |> We need to store a person's name, date of birth, etc. A person may have
|> |> multiple names (e.g., current name, maiden name, preferred name.)
|> |> Some possible designs are:
|> |>
|> |>
|> |> 2. create table person_table
|> |> ( person_id int, /* primary key */
|> |> birth_date datetime
|> |> )
|> |>
|> |> create table person_name
|> |> ( /* compound primary key: person_id + name_type */
|> |> person_id int, /* foreign key */
|> |> name_type char(10),
|> |> name varchar(30)
|> |> )
|> |>
|> |> ADVANTAGE:
|> |> A normalized design. Great for classroom teaching.
|> |> DISADVANTAGE:
|> |> Not guaranteed that every person in person_table has a name in
|> |> person_name table. We don't want that to happen.
|> |>
|>
|> How about explicitly declaring name NOT NULL. Using the example above this would yeild:
|>
|> create table person_name
|> ( /* compound primary key: person_id + name_type */
|> person_id int NOT NULL, /* foreign key */
|> name_type char(10) NOT NULL,
|> name varchar(30) NOT NULL
|> )
|>
|> This would eliminate the disadvantage since Sybase would require an entry for name (in addition
|> to person_id and name-type)
|>
|> --
|> Lonnie Barnett
|> Mead Data Central (513) 865-1038
|> P. O. Box 933 lonnie_at_meaddata.com
|> Dayton, Ohio 45401 ...!uunet!meaddata!lonnie

The problem with the above solution is that it still allows an entry in the person_table table without any corresponding entry in the person_name table. As I understand it, this is the problem that the original author wants solved.

There is no way to build this constraint through standard SQL. However, most commercial products (e.g. Sybase, Oracle, Ingres) allow you to build the constraint into the database as a trigger or a package. A package would work best, where the user or application would specify:

new_person(name, name_type, birthdate)

and the package would generate the person_id key.

Hope this helps.

-- 
Derek Fields
(derek_at_gamekeeper.bellcore.com)