Re: RFC: Product directory

On Mon, Jun 02, 2008 at 09:30:58AM +0100, Dave Page wrote:
> I'm looking at replacing the ad-hoc lists of products at
> http://www.postgresql.org/download/ and
> http://www.postgresql.org/download/commercial with a product
> directory. I'd like to keep it fairly simple, and propose the
> following data be stored:
>
> id serial primary key -- easier in the framework, complaints to /dev/null
> publisher text -- Company/person/project name
> publisher_url text -- Company/person/project URL
> product text -- Product name
> product_url text -- URL for the product
> category int4 -- Category ID (fkey -> categories table)
> description text -- Product description
> price text -- Pricing info (where relevant)

Price is too complicated to model, and suffers from "cache coherency"
issues. Pointing to a web site, where appropriate, would handle this
better.

> licence char(1) -- Licence type flag

CREATE TABLE category (
category_id SERIAL PRIMARY KEY,
category_name TEXT NOT NULL
);

CREATE UNIQUE INDEX unique_category_idx
ON category(trim(lower(category_name)));

CREATE TABLE license (
license_id SERIAL PRIMARY KEY,
license_name TEXT NOT NULL,
license_text TEXT NOT NULL
);

CREATE UNIQUE INDEX unique_license_idx
ON license(trim(lower(license_name)));

CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
product_name TEXT,
product_url TEXT,
publisher TEXT,
publisher_url TEXT,
description TEXT
);

CREATE TABLE product_category (
product_id INTEGER NOT NULL REFERENCES product(product_id),
category_id INTEGER NOT NULL REFERENCES category(category_id),
);

CREATE TABLE product_license (
product_id INTEGER NOT NULL REFERENCES product(product_id),
license_id INTEGER NOT NULL REFERENCES license(license_id),
);

CREATE VIEW product_overall AS
SELECT
p.product_name,
p.publisher,
p.publisher_url,
p.product_url,
p.description,
array_accum(l.category_name) AS "Category(s)",
array_accum(l.license_name) AS "License(s)"
FROM
product p
LEFT JOIN
product_category pc
ON (p.product_id = pc.product_id)
RIGHT JOIN
category c
ON (pc.category_id = c.category_id)
LEFT JOIN
product_license pl
ON (p.product_id = pl.license_id)
RIGHT JOIN
license l
ON (pl.license_id = l.license_id)
GROUP BY
p.product_id,
p.publisher,
p.publisher_url,
p.product,
p.product_url,
p.description;

> The categories table would simply be a lookup table of category names:
>
> Administration/development tools
> Programming interfaces
> Clustering/replication
> Procedural languages
> Reporting tools
> PostgreSQL extensions
> Applications
> ??
>
> The licence type codes will be hardcoded:
>
> 'o' - Open Source
> 'c' - Commercial
> 'f' - Freeware
>
> Sound reasonable? Anything I've missed?

Products may have more than one category and more than one license.
The above schema handles these things. Might we want to break
"publisher" out into a separate table?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate