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