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

carloschoenberg_at_yahoo.com wrote:

> 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.

Just ask yourself what happens without one of them - can you you cope with that? In this case: say you get rid of stuff_id in both tables. Nope, not nice.
Say you get rid of stuff_appearance_id in both tables. Hey! No problem. If there is a row in stuff_appearance it just means there is a stuff_appearance. Nice :-)

Now you may need stuff_appearance_id for other purposes. I can't tell, because I have very limited clues as to what the content of these tables means. From these limited clues, though, I can say: For your 1-to[0 or 1] mapping you don't need it.

> 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.

What does the existence of a row in table stuff imply? Does it mean you are capable of handling it or does it mean it's there? The name 'stuff_appearance' for the other table suggests the latter. It is much better to make those interpretations explicit. Modelling becomes far easier.

> 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.

Similar questions here: What does stuff_location_id mean? Is where you would have the stuff appear if it would appear? What do you need to know about the 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.

Tip: assess the meaning, do not rely on the syntax of some arbitrary language to remind you of all aspects relevant in modeling. Received on Sat May 08 2004 - 10:45:40 CEST