Re: foreign key references on view

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:e1v0augmvqs9o78bi1jjuvokbhp8c5q6h1_at_4ax.com...
> On Tue, 26 Mar 2002 14:20:21 +0100, zorg <czorman_at_wanadoo.fr> wrote:
...
> A more or less straightforward solution looks like
> merge the two tables into one, adding an extra column, which will
> contain a switch indicating which type of record you have.
> Define views referring to the value of the switch for each subtype you
> have in your table.

Or leave the two tables pretty much as-is and just add another table with the numcon and subtype columns. In the future if there are any other columns "shared" by the individual and group tables, you may want to put these in the new supertype table, too. This implementation segregates columns to the appropriate table and avoids having columns that are not applicable to the type of thing being stored. On the downside, it costs you a join in order to get all the columns for the type of thing being stored.

Sybrand's solution avoids the join and for the simple DDL you presented, would be a better solution. But if your model becomes more complex with more subtypes and more attributes, you should consider the multiple table solution.

BTW, this is the classical problem of implementing inheritance in a relational database. Sybrand is proposing "horizontal/supertype consolidation" and I am proposing "vertical/subtype segregation". These are the extremes of the possible solutions. The actual solution you should implement may be between these extremes and depends on how you use your data.

And finally, you can also leave the tables as-is and bag the foreign key. Use triggers to enforce the rule.

--
Later,
BEDick