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?
