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
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