Re: How do people usually solve this?
From: Bob Truman <truman_at_provost-admin.uwaterloo.ca>
Date: Fri, 7 Jan 1994 13:59:21 GMT
Message-ID: <CJ9Iux.ILz_at_watserv1.uwaterloo.ca>
The following discussion/question is very interesting. Has anyone considered and/or solved a similar issue -- date stamping in tables... For example, the name issue could be one of change_on_marriage. If there were some simple way to show that the unmarried name was effective from X to Y then a new name effective from Y+1 to ??, then the multiple name problem is solved. The issue of date stamping can be general. Consider a department code - dept name table, for the first n years, the department name is X, then it changes to Y. Reports may be required that use the correct name during the appropriate time period.
>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
Received on Fri Jan 07 1994 - 14:59:21 CET
Date: Fri, 7 Jan 1994 13:59:21 GMT
Message-ID: <CJ9Iux.ILz_at_watserv1.uwaterloo.ca>
The following discussion/question is very interesting. Has anyone considered and/or solved a similar issue -- date stamping in tables... For example, the name issue could be one of change_on_marriage. If there were some simple way to show that the unmarried name was effective from X to Y then a new name effective from Y+1 to ??, then the multiple name problem is solved. The issue of date stamping can be general. Consider a department code - dept name table, for the first n years, the department name is X, then it changes to Y. Reports may be required that use the correct name during the appropriate time period.
Any general solutions? Advice?
Bob Truman
Director, Operations Analysis
University of Waterloo.
In article <2ghgba$cqm_at_meaddata.meaddata.com>, Lonnie Barnett <lonnie_at_meaddata.com> wrote:
>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
Received on Fri Jan 07 1994 - 14:59:21 CET