M:N relationship with canonical pairing
Date: 28 Apr 2003 07:23:32 -0700
Message-ID: <b4cc5e7c.0304280623.66412d8f_at_posting.google.com>
Concrete background: I'm designing some tables in a relational DB (mysql) that address the "name authority problem": (1) each person has one or more names (e.g. George Bush, George W. Bush, George Walker Bush; or a change in last name after marriage); (2) a particular name refers to one or more people (perhaps George Bush could refer to both George Walker Bush, born 1943, and to his father).
I think it best to keep the concepts of "person" and "name" separated.
That would lead to 3 tables:
* 'person'
- 'name'
- 'person_name' (the M:N relationship tying persons and names)
I'd like to have the following two design constraints:
* Participation of person in 'person_name' is total (ie, each person
must have at least one name);
* Participation of name in 'person_name' is total (ie, each name must
refer to a person);
* For each person, one name in particular is "canonical" (i.e, current
and full).
I'd like to build as much of these semantic constraints into the
tables in as natural a way as possible. However,
* There doesn't seem to be a good way to enforce participation
constraints in most RDMSs, insofar as a standard way to enforce these
in SQL is to use a CREATE ASSERTION statement (with nested EXISTs);
but most RDMSs don't have CREATE ASSERTION;
* I'm not sure how to enforce the constraint "each person must have
exactly one canonical name" in as natural a way as possible.
One possibility is to stick a foreign key into 'person' for the canonical name; but then (if you want to make the DB semantics as consistent as possible) I assume you'd want a constraint saying that there must be a row for that name in table 'person_name' pointing back. (I.e., there is a "subset" of the M:N relationship which is 1:1.)
Naively one might say "create an additional relation, 'canonical_name', between persons and names," but AFAICT this doesn't help avoid with the consistency issues, since you'd have two tables (person_name and canonical_name) that are closely related...
Of course, a cheap way to do any of this is to
* use the foreign key solution above;
- write some code in SQL that just checks the constraints every so often, but that doesn't look clean (ie, it's not a constraint enforced by the designs of the tables themselves)
TIA, sjfromm Received on Mon Apr 28 2003 - 16:23:32 CEST