Ensuring data consistency - main table and multiple 'details' tables.

Поиск
Список
Период
Сортировка
От Daniel M.
Тема Ensuring data consistency - main table and multiple 'details' tables.
Дата
Msg-id 4137A60C.6020102@nm.ru
обсуждение исходный текст
Список pgsql-general
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?

В списке pgsql-general по дате отправления:

Предыдущее
От: Kevin Murphy
Дата:
Сообщение: Re: zombie primary key lurches out of database to devour the brains of the unwary
Следующее
От: Tom Lane
Дата:
Сообщение: Re: The future of built-in geometric data types