On Thu, Jun 14, 2012 at 2:08 AM, Yuriy Rusinov <yrusinov@gmail.com> wrote:
> Hello, Jeff !
>
>>
>> One foreign key cannot reference two tables.
>>
>> Have you considered a design that does not use inheritance? For
>> instance, the users table could reference q_base_table, and then
>> record_rubricator could also reference q_base_table?
>
> Unfortunately not, because this design does not developed by me and
> these changes will result to inefficiency of all project.
>>
>> Also, I don't understand what you mean about propagating data. What data
>> do you want to propagate?
>
> We're need common numeration for primary key for all users tables, but
> others columns may be different for tables.
>
When we forked LedgerSMB from SQL-Ledger they used a similar design
and it was a mess. I don't want to go into the problems we figured
could exist or we heard about from users.
Luckily if all you are looking for is a common place for primary keys
there is a useful shim that we came up with:
1) Create a table that stores the pkey value and what table it's in.
2) Use triggers on other tables to maintain that data
3) Use fkeys against either the other tables themselves (where it is
a local reference) or against the common table
It's not really ideal. It's possible the data will get out of sync of
something goes wrong and then you will have problems but it seems to
work for the most part and without a significant performance headache.
Best Wishes,
Chris Travers