Composite types or composite keys?

From: Tony Theodore <tony(dot)theodore(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org general" <pgsql-general(at)postgresql(dot)org>
Subject: Composite types or composite keys?
Date: 2013-11-15 08:01:29
Message-ID: 6D46FD23-2011-4E52-BD54-09FC54A0C117@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this:

create table products (
source_system text,
product_id text,
description text,
...
primary key (source_system, product_id)
);
create table inventory (
source_system text,
product_id text,
qty int,
...
foreign key (source_system, product_id) references products
);

and it means having to add the “source_system" column to many queries. Would something like:

create type product as (
source_system text,
product_id text
);
create table products (
product product,
description text,
...
primary key(product)
);
create table inventory (
product product,
qty numeric,
...
foreign key (product) references products
);

be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice.

Thanks,

Tony

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jayadevan M 2013-11-15 09:38:53 Postgres Server backend process
Previous Message Philippe Girolami 2013-11-15 06:43:53 Re: Push predicate down in view containing window function