Re: Foreign key constraint question

Jeff Davis skrev:
> On Fri, 2007-07-20 at 19:18 -0500, Perry Smith wrote:
>>> The relational model handles inheritance and polymorphism very well if
>>> you don't store types as values.
>> What if I have just an id for an item? This will happen when another
>> table references an item. How do I know what type it is? Are you
>> suggesting I look in companies, people, etc, etc to find the type?
>> It would seem better to have a table that tells me the type. Then
>> retrieve the item from the specified table.
>
> Why do you need to know the type? The purpose of polymorphism is that,
> if you are looking to access a set of polygons, you don't care whether
> an individual shape is a triangle or a square, all you care is that it's
> a polygon.
>
> If you want to access triangles specifically, you join polygons to
> triangles.

What if, for instance, I want to render a list of shapes?

To render the shape, I need to get its data, to get its data, I need to
know what type it is. ISTM that the easiest way to achieve this is
storing the type info at the "top" of the table hierarchy.

This then gives us two evils to choose from wrt integrity checking:

1. Add the type column to these tables as well, restricting possible
values. This gives us integrity at the cost of verbosity and lack of
modularity (a type need to "know" its subtypes).

2. Leave integrity enforcement to the applications (and possibly write
stored procedures for it).

Both of these are reasonable implementation choices, I would say.

The OP wants to do 1, is only envisioning one level of inheritance, and
wants a shortcut for it.

Yours,

Nis Jorgensen

PS: Hi Jeff. Small world, isn't it?