Re: General question

What kind of account are we talking about? A user account, an accounting account, a customer account, something else?

IF you were to use a non-shared foreign key in the application_account table which primary key would you use within the other tables in the application if you need to refer to account?

David J.

From: salah jubeh [mailto:s_jubeh(at)yahoo(dot)com]
Sent: Wednesday, March 23, 2011 10:29 AM
To: David Johnston
Cc: pgsql
Subject: Re: [GENERAL] General question

Dear Johnston,

Thanks for the reply, I really get a lot of benefit from it. In my design, I have several accounts which share some information at least id an the name. So, I want to make a specialization tree. Also, I want to use the ids as a global Identifiers in different scopes. So, I want to indicate that account 1 is the same in all applications even though it has a different role. This will facilitate reporting and tracking because my company provides many services.

I can implement the design also, with inheritance. But I do not prefer to use it, because it will complex the porting to another database system.

Regards

_____

From: David Johnston <polobo(at)yahoo(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>; pgsql <pgsql-general(at)postgresql(dot)org>
Sent: Wed, March 23, 2011 2:58:54 PM
Subject: RE: [GENERAL] General question

The main significant advantage that NOT making the primary key also a foreign key is that you can set the foreign key reference to ON DELETE SET NULL. If they are shared this will not work since a primary key cannot be NULL.

However, if you are going to do “ON DELETE CASCADE” anyway then the main question is whether and why you have the second table. From a data semantics stand-point if the two tables truly represent the same thing but for some reason need to be separated then using a shared key reinforces that fact.

If you apply referential integrity then no data anomalies can exist; semantic anomalies always can if you do not understand what is being modeled but simply using a 1-1/shared-key does not make the model invalid.

As an example: much of my work is dealing with external systems. When I import data from those systems I store that information onto its own table (…core). Often I have a need to generate additional data (…extended) related to the original. In those cases I’ll often do a shared key. Both the core and extended record represent the same entity but I place the data onto two tables since one represents original source data and one represents calculated data. If the original source record goes away I have no context for the extended data and if a new source record is inserted the process by which I do the insert regenerates the extended data. Thus it is not necessary to keep the extended record in place.

However, there are some occasions where I generate extended data that does want to outlive the deletion of the source record. This occurs often if the source record is able to be changed. For simple requirements I’ll just delete the original source record and then insert the changed record. I then have/need a process to re-link the new source with the original extended record. In this case the extended data is not system generated but user generated (so it cannot be refreshed automatically). Also, It is possible that the changed source record no longer matches up with the existing extended data and so an automatic relinking process is not desirable. I call those unlinked foreign table records “Orphans”.

If you are unsure, using a different field for the Foreign Key is more flexible, and you can always hide much of the complexity behind a view, but using a shared key has the advantage of clearly showing that the two tables represent properties for identical entities but that some meta-data like factor necessitates keeping the data on two separate tables (otherwise you should just put them onto the same table).

David J.

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of salah jubeh
Sent: Wednesday, March 23, 2011 8:18 AM
To: pgsql
Subject: [GENERAL] General question

Hello,

Some times the primary key is the same as the foreign key such as in the following design. which is used to model 1-1 relationship.

In the database books, such as database fundamentals(Masri), the 1-1 relation is modeled by having two separate key.

when this kind of design (shared key) is preferable and is there any anomalies to it.

create table Table1

( T1_Id integer not null primary key

, T1_Data varchar(9) not null

)

create table Table2

( T2_Id integer not null primary key

, T2_Data varchar(37) not null

, foreign key (T2_Id) references Table1 (T1_Id)

)

Thanks in advance