Re: General question

It is a user accounts, which might then become customer accounts, accounting
accounts, etc. I will use specialization and generalization concepts in
database. I did not complete the design analyses but most probably, I will use
shared keys.

Regards

________________________________

From: David Johnston <polobo(at)yahoo(dot)com>
To: salah jubeh <s_jubeh(at)yahoo(dot)com>
Cc: pgsql <pgsql-general(at)postgresql(dot)org>
Sent: Wed, March 23, 2011 3:46:24 PM
Subject: RE: [GENERAL] 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