Avoiding duplications in tables

Hi Deepa,

Let me know that
1) Is tables ApplicationNumber and ApplicationManager are same ?
2) Why do you have more than one referential key between two tables, Which
will makes process very slow.

If answer to the first question as 'yes' and answer to the second question
as 'I can change it to single referential key'
You can look the following design which will wipe out redundancy.

(1) tablename : versions

versionnumber - string

(2) tablename : applications

applicationnumber - integer
other details

(3) tablename : applicationnumber -- CHANGED

ApplicaitonNumberId Integer
versionnumber - string
applicationnumber - integer

(4) tablename : profilemanager -- CHANGED

ApplicationNumberId - Integer
ProfileId - Integer
other details

(5) tablename : profiles -- CHANGED

ProfileId - Integer
versionnumber - string
profilename - string

Primary key :
---------------

(1) versions : versionnumber
(2) applicationmanager ( ApplicationNumber) : ApplicationNumberId --
CHANGED
(3) applications : applicationnumber
(4) profilemanager : ProfileId, ApplicationNumberId -- CHANGED
(5) profiles : ProfileId -- CHANGED

Relations :
-----------

(1) versionnumber of 'applicationmanager/ApplicationNumber' referes to
versionnumber of

'versions' table.
(2) applicationnumber of 'applicationmanager/ApplicationNumber' referes
to applicationnumber of 'applications' table.
(3) ApplicationNumberId of 'profilemanager' referes to
ApplicationNumberId of 'applicationmanager'. -- CHANGED
(4) ProfileId of 'profilemanager' referes to ProfileId of 'profiles'. --
CHANGED
(5) versionnumber of 'profiles' referes to versionnumber of 'versions'.

I Think this design will serve your purpose (which will move redundancy).

Sreedhar Baskararaju

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
----- Original Message -----
From: "Deepa K" <kdeepa@midascomm.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, March 24, 2003 12:33 PM
Subject: [GENERAL] Avoiding duplications in tables

Look for articles on 'surrogate primary keys'. In the system you have below, if
you change profilename, or any other string value that's used as a key, your
database has to change all of those in all fields. With a surrogate key, that
doesn't happen.

This reduces database loading, and also the likelyhood of errors. Also,looking
things up by strings in searches is slower. If you use a surrogate key, then
only ONE search for the string is done, and the remaining searches are done for
an integer, MUCH faster. The 'serial' datatype is made for this.

it does complicate your SQL statements and require subselects, however. But
among open source DB's, Postgres stands out as the one that has supported
subsellects and foreign keys the longest and most thoroughly, so you're in luck.

Here is the same table that I used for a create table syntax example, which you
can now look at for a surrogate key example:

CREATE TABLE PhonNums(
phone_num_id serial NOT NULL PRIMARY KEY,
phon_num varchar(32) NOT NULL UNIQUE,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Deepa K wrote: