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_urlTEXT,   description TEXT
 
);

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

CREATE TABLE product_license (   product_id INTEGER NOT NULL REFERENCES product(product_id),   license_id INTEGER NOT
NULLREFERENCES 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_licensepl       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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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