Composite types or composite keys?

[Tony Theodore <tony.theodore@xxxxxxxxx>]

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



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general