Re: How to ensure data consistency?

"ddtl" <fake_at_address.com> wrote in message news:3lmnj0tdqs6n98so05hslbs6b0rjqkkdk7_at_4ax.com...
>
> Hello everybody,
>
> A table T contains list of items and their common properties.
> Some of those items in the list also have additional
> properties. Let's say that there are 3 groups of items
> - group 'a' doesn't have any additional properties, group
> 'b' has 2 additional properties, and group 'c' has 3
> additional properties. In order to be able to store
> information about those additional properties, I create 2
> additional tables (let's call them B and C). Primary key
> in those tables is the same as in the table T, but it is
> also a foreign key, so that user won't be able to enter
> information about items which are not in the table T.
>
> The question is, how can I make sure that each item
> which belongs to groups 'b' or 'c' has an entry (only)
> in an appropriate additional table, and that each item
> which belongs to group 'a' doesn't have an entry in any
> of those additional tables?
>
> There is another complication - when I create
> all those tables, I don't know how many different groups
> the table T is going to contain - the groups are created
> when new items which have a set of additional common properties
> are inserted in the table T (but once created, groups are never
> deleted). It is clear, of course, that i will have to
> create a new additional table when the new group is created,
> but (in case the above problem has a solution) I also
> don't want to break anything that worked before.
>
> Is it possible to ensure data consistency in such a case,
> and if yes - how it can be done?
>
> BTW, I use postgresql.
>
>

Everybody: Is this the gen-spec question again? Received on Mon Sep 06 2004 - 00:35:29 CEST