where to put foreign keys in 1-to-[0 or 1] 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.

CREATE TABLE stuff (

    stuff_id serial unique primary key,
    stuff_name varchar(256) unique,
    stuff_appearance_id integer references stuff_appearances,
    stuff_location_id integer references stuff_locations,
    stuff_type_id integer references stuff_types

);

CREATE TABLE stuff_appearances (

    stuff_appearance_id serial unique primary key,     stuff_id integer references stuff,
    pointiness integer,
    shininess integer,
    fuzzy boolean,
    smell_description varchar(1024)

);
Received on Sat May 08 2004 - 08:35:37 CEST