Re: Avoiding duplications in tables - Mailing list pgsql-general

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
>