pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexandre Leclerc <alexandre.leclerc@gmail.com>
To: PERFORM <pgsql-performance@postgresql.org>
Subject: Data type to use for primary key
Date: Mon, 22 Nov 2004 15:32:40 -0500
Message-ID: <1dc7f0e30411221232603c4617@mail.gmail.com> (raw)

Good day,

I'm asking myself if there is a performance issue in using an integer
of varchar(24) PRIMARY KEY in a product table.

I've read that there is no speed issue in the query, but that the only
performance issue is the database size of copying the key in other
tables that require it.

My product_id is copied in orders, jobs, and other specific tables.

What is the common approach? Should I use directly the product_code as
my ID, or use a sequantial number for speed? (I did the same for the
company_id, this is a 'serial' and not the shor name of the customer.
I just don't know what is usually done.

Right now I did the following:
CREATE TABLE design.products (
product_id          serial      PRIMARY KEY,
company_id          integer     NOT NULL REFERENCES sales.companies ON
UPDATE CASCADE,
product_code        varchar(24) NOT NULL,
...
CONSTRAINT product_code_already_used_for_this_company UNIQUE
(company_id, product_code)
);

CREATE TABLE sales.companies (
company_id        integer      PRIMARY KEY,
company_name      varchar(48)  NOT NULL UNIQUE,
...
);

The company_id is also copied in many tables like product, contacts, etc.

Thank you very much for any good pointers on this 'already seen' issue.

-- 
Alexandre Leclerc



view thread (14+ messages)  latest in thread

Message-ID: <1dc7f0e30411221232603c4617@mail.gmail.com>
Permalink:  ../1dc7f0e30411221232603c4617@mail.gmail.com/
Also on:    postgresql.org/message-id/1dc7f0e30411221232603c4617@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: alexandre.leclerc@gmail.com
  Subject: Re: Data type to use for primary key
  In-Reply-To: <1dc7f0e30411221232603c4617@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox