Thread: Avoiding duplications in tables
Hi All,
I have the following tables.
(1) tablename : versions
versionnumber - string
(2) tablename : applications
applicationnumber - integer
other details
(3) tablename : applicationnumber
versionnumber - string
applicationnumber - integer
(4) tablename : profilemanager
versionnumber - string
profilename - string
applicationnumber - integer
other details
(5) tablename : profiles
versionnumber - string
profilename - string
Primary key :
---------------
(1) versions : versionnumber
(2) applicationmanager : versionnumber, applicationnumber
(3) applications : applicationnumber
(4) profilemanager : veriosnnumber, profilename, applicationnumber
(5) profiles : versionnumber, profilename
Relations :
-----------
(1) versionnumber of 'applicationmanager' referes to versionnumber of
'versions' table.
(2) applicationnumber of 'applicationmanager' referes to
applicationnumber of 'applications' table.
(3) versionnumber and applicationnumber of 'profilemanager' referes
to versionnumber and applicationnumber of
'applicationmanager'.
(4) versionnumber and profilename of 'profilemanager' referes ot
versionnumber and profilename of 'profiles'.
(5) versionnumber of 'profiles' referes to versionnumber of
'versions'.
In the above design I can able to see duplication of data in all the
tables. If I take out a id out of all these tables to make reference, is
that problem will solve. Is that is
a correct approach.
Can any one help me.
regards,
Deepa K
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
> Hi All,
> I have the following tables.
>
> (1) tablename : versions
>
> versionnumber - string
>
> (2) tablename : applications
>
> applicationnumber - integer
> other details
>
> (3) tablename : applicationnumber
>
> versionnumber - string
> applicationnumber - integer
>
> (4) tablename : profilemanager
>
> versionnumber - string
> profilename - string
> applicationnumber - integer
> other details
>
> (5) tablename : profiles
>
> versionnumber - string
> profilename - string
>
> Primary key :
> ---------------
>
> (1) versions : versionnumber
> (2) applicationmanager : versionnumber, applicationnumber
> (3) applications : applicationnumber
> (4) profilemanager : veriosnnumber, profilename, applicationnumber
> (5) profiles : versionnumber, profilename
>
> Relations :
> -----------
>
> (1) versionnumber of 'applicationmanager' referes to versionnumber of
>
> 'versions' table.
> (2) applicationnumber of 'applicationmanager' referes to
> applicationnumber of 'applications' table.
> (3) versionnumber and applicationnumber of 'profilemanager' referes
> to versionnumber and applicationnumber of
> 'applicationmanager'.
> (4) versionnumber and profilename of 'profilemanager' referes ot
> versionnumber and profilename of 'profiles'.
> (5) versionnumber of 'profiles' referes to versionnumber of
> 'versions'.
>
> In the above design I can able to see duplication of data in all the
> tables. If I take out a id out of all these tables to make reference, is
>
> that problem will solve. Is that is
> a correct approach.
>
> Can any one help me.
>
> regards,
> Deepa K
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
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:
> Hi All,
> I have the following tables.
>
> (1) tablename : versions
>
> versionnumber - string
>
> (2) tablename : applications
>
> applicationnumber - integer
> other details
>
> (3) tablename : applicationnumber
>
> versionnumber - string
> applicationnumber - integer
>
> (4) tablename : profilemanager
>
> versionnumber - string
> profilename - string
> applicationnumber - integer
> other details
>
> (5) tablename : profiles
>
> versionnumber - string
> profilename - string
>
> Primary key :
> ---------------
>
> (1) versions : versionnumber
> (2) applicationmanager : versionnumber, applicationnumber
> (3) applications : applicationnumber
> (4) profilemanager : veriosnnumber, profilename, applicationnumber
> (5) profiles : versionnumber, profilename
>
> Relations :
> -----------
>
> (1) versionnumber of 'applicationmanager' referes to versionnumber of
>
> 'versions' table.
> (2) applicationnumber of 'applicationmanager' referes to
> applicationnumber of 'applications' table.
> (3) versionnumber and applicationnumber of 'profilemanager' referes
> to versionnumber and applicationnumber of
> 'applicationmanager'.
> (4) versionnumber and profilename of 'profilemanager' referes ot
> versionnumber and profilename of 'profiles'.
> (5) versionnumber of 'profiles' referes to versionnumber of
> 'versions'.
>
> In the above design I can able to see duplication of data in all the
> tables. If I take out a id out of all these tables to make reference, is
>
> that problem will solve. Is that is
> a correct approach.
>
> Can any one help me.
>
> regards,
> Deepa K
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>