Re: where to put foreign keys in 1-to-[0 or 1] mapping

<carloschoenberg_at_yahoo.com> wrote in message news:8c526b62.0405072235.399a2dff_at_posting.google.com...
> I have a table with about 25 columns. The data can be separated into
> about 6 logically distinct tables. One of these 6 tables is the
> "master", the table "stuff" below. I call it the master because
> records will always either be looked up by stuff_name, or looked up by
> some other criteria with the intent of getting a list of qualifying
> stuff_names. The other 5 tables make no sense without the master,
> unless you already remember a stuff_name to stuff_id mapping.
>
> In the example below, stuff_appearances is linked to stuff both by
> stuff.stuff_appearance_id and by stuff_appearances.stuff_id. Obviously
> I only want to do one of these, not both. Which is better? An entry in
> stuff will either have zero or one stuff_appearances entry. Even if
> two entries in stuff shared the same
> pointiness/shininess/fuzzy/smell_description, it would be coincidence.
> I wouldn't want to treat them as one type. I wouldn't want to update
> the shininess of both if only the shininess of one changed. There are
> several other tables like this, not shown in the example.
>
> stuff_location_id is more borderline. Objects will share locations
> because the location identifier is less granular than the size of an
> object. But if one is moved, it doesn't affect the location of others
> who used to share a location.
>
> stuff_type_id is easy, there are only about 5 rows in stuff_types. In
> practice stuff.stuff_type_id will not be null.
>
> I am concerned only with creating a robust, clean, understandable, and
> maintainable design.

The tip I can give is that even though the conceptually simplest way to think is in terms of reciprocol links (recognizing a link from me to my bike and my bike to me), even if it were a case of a 1-1 mapping in an RDBMS, you pick one (typically not the "master" -- a non-relational concept, but ...) where you put the foreign key to the other. Because you won't be using navigation functions, but only set functions, you don't specify both directions.

In this case it sounds like you have some M-1 mappings from "master" to other tables so that, for example, more than one "stuff" can be associated with a single location. Because there is no provision (again, in an RDBMS) for a LIST of foreign keys, you put the foreign key information on the "M" side where it maps to just 1 (as in the case of location).

I hope that helps. --dawn Received on Sat May 08 2004 - 15:24:50 CEST