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

mAsterdam <mAsterdam_at_vrijdag.org> wrote in message news:<409c9e37$0$557$e4fe514c_at_news.xs4all.nl>...
> 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 :-)

I do like it this way. It seems more sensible in my situation.

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

stuff_appearance_id would serve no purpose other than to link these two tables, in this case.

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

It means it's there. The only indication of being capable of handling it is a listing in stuff_types. The existence of a row in stuff_appearances means that for that item its appearance is known and has been recorded. A particular appearance is not predefined.

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

It's where the stuff currently is, or null if its location is not known. A location will be a site id (referencing a sites table that has the geographic location of this site) and site-specific location data. I'm representing the site-specific data right now as room, x, y. For a given site, the rooms are predefined. Given a site/room, you know what x and y mean (e.g. coordinates, rack/shelf, office#/NULL, row/bay). Multiple items can share a site/room/x/y.

It is possible that, in the future, it will be desirable to track every location in certain rooms, to determine where available space is. This would require the addition of a z and the listing of every valid x/y/z for that room in the locations table. Each location would either be empty, "dead" (unusable) space, or containing a stuff_id. A stuff could span multiple contiguous z, however it could only be in one site/room/x/y.

This possible change would seem to make it clear that stuff_id should be in the location table, and no location_id should be stored in the stuff table. However the risk that this change will be required is low, so if I am sacrificing something for it, it may not be worthwhile.

Thanks for the advice. Received on Sat May 08 2004 - 18:40:47 CEST