X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 354B93A5289 for ; Mon, 22 Nov 2004 20:32:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66196-07 for ; Mon, 22 Nov 2004 20:32:40 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by svr1.postgresql.org (Postfix) with ESMTP id A87873A535E for ; Mon, 22 Nov 2004 20:32:40 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 40so171056wri for ; Mon, 22 Nov 2004 12:32:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding; b=ahbHVd9I8ax0ecezZxR6MXSwJI+7YorvDNSmjBkMfgsCfqtWo4R72Skuy0nlJK3M6x1/V7+tv0cGeQJe4sWERRyoh98Y53YL3dqQyeP5hBiE/Wz3yyhWs94E4XUPyVYNHen1lmbmIpTohkUYkp0D2rP9Eb3bTwDmt/6CQcu5B1U= Received: by 10.54.17.5 with SMTP id 5mr869794wrq; Mon, 22 Nov 2004 12:32:40 -0800 (PST) Received: by 10.54.20.23 with HTTP; Mon, 22 Nov 2004 12:32:40 -0800 (PST) Message-ID: <1dc7f0e30411221232603c4617@mail.gmail.com> Date: Mon, 22 Nov 2004 15:32:40 -0500 From: Alexandre Leclerc Reply-To: Alexandre Leclerc To: PERFORM Subject: Data type to use for primary key Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/336 X-Sequence-Number: 9290 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