Re: How to ensure data consistency?

Hi ddtl,

I have a similar problem, although mine is about enforcing "complete disjoint specialization", whereas yours is about enforcing "incomplete disjoint specialization" (terminology is from the article that you pointed to).

On Tue, 07 Sep 2004 11:16:13 -0700 ddtl wrote:
> Thanks, I found what I was looking for - which is not what I hoped to
> find, though - according to that article:
>
> http://www.tomjewett.com/dbdesign/dbdesign.php?page=subclass.php&imgsize=largest
>
> it is impossible to enforce specialization constraints, and the only
> way to ensure that every item with additional properties in the
> super-table has an entry in the appropriate sub-table, is in the front
> end and not in a database itself :-(.

Well, it looks as if this could be done using triggers. I don't know how to code it, though, since I've no experience with SQL. Another approach would be using a different data model, illustrated below. However, there still needs to be validation (with triggers?) that the id's of the specified specialization are non null.

TS
t_id (PK)
specialization_type: Either "a", "b", or "c". b_id (FK)
c_id (FK)
[...]

BS
b_id (PK)
[...]

CS
c_id (PK)
[...]

Comments?

Felix Received on Tue Sep 07 2004 - 15:49:57 CEST