Обсуждение: Using duplicate foreign keys

I have multi-company database.
Each company has its own chart of accounts table which are stored in each
company schema.
Some account numbers are used in a common table which is stored in public
schema.

So I need to create duplicate foreign keys like

create temp table company1.chartoffaccounts ( accountnumber int primary
key);
create temp table company2.chartoffaccounts ( accountnumber int primary
key);

create temp table public.commontable ( accountnumber int,
   FOREIGN KEY (accountnumber)   REFERENCES c1hartoffaccounts
(accountnumber)
   FOREIGN KEY (accountnumber)   REFERENCES c2hartoffaccounts
(accountnumber) );


Is it OK to use duplicate foreign keys ? What issues will they cause ?

Andrus.

Andrus wrote:
> I have multi-company database.
> Each company has its own chart of accounts table which are stored in each
> company schema.
> Some account numbers are used in a common table which is stored in public
> schema.
>
> So I need to create duplicate foreign keys like
>
> create temp table company1.chartoffaccounts ( accountnumber int primary
> key);
> create temp table company2.chartoffaccounts ( accountnumber int primary
> key);
>
> create temp table public.commontable ( accountnumber int,
>    FOREIGN KEY (accountnumber)   REFERENCES c1hartoffaccounts
> (accountnumber)
>    FOREIGN KEY (accountnumber)   REFERENCES c2hartoffaccounts
> (accountnumber) );
>
>
> Is it OK to use duplicate foreign keys ? What issues will they cause ?
>
> Andrus.
>
The problem I see with that is that any value of accountnumber in
public.commontable would need to be in both company1.chartoffaccounts
and company2.chartoffaccounts.  One key referencing two completely sets
of data?  That sounds broken.  Can you give a more detailed example of
how you want to use this?   Off hand, it sounds like what you may want
is to have accountnumber as the primary key of public.commontable with
the accountnumber columns in the schema specific tables referencing it
instead.

--
erik jones <erik@myemma.com>
software development
emma(r)

> The problem I see with that is that any value of accountnumber in
> public.commontable would need to be in both company1.chartoffaccounts and
> company2.chartoffaccounts.  One key referencing two completely sets of
> data?  That sounds broken.  Can you give a more detailed example of how
> you want to use this?

public.commontable describes rules how invoices are posted to general
ledger.
It seems not reasonable to duplicate this table in every company schema
since it contains usually same data for every company.
public.commontable contians fixed account numbers which I think I must force
to be present in chart of accounts in every company schema to use this kind
of foreign key relationship.

> Off hand, it sounds like what you may want is to have accountnumber as the
> primary key of public.commontable with the accountnumber columns in the
> schema specific tables referencing it instead.

public commontable contains also a lot of other columns describing invoice
posting rules.
accountnumber cannot be primary key of  public.commontable since it can
contain same account numbers for different types of documents.

Yes, it seems to be possible to create a new table
public.commonaccountnumbers  with accountnumber as primary key.

So the question seems to be:

can I use duplicate foreign keys without problems or must I waste resources
to create new table commonaccountnumbers ?


Andrus.