public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Fetter <[email protected]>
To: Dave Page <[email protected]>
Cc: w^3 <[email protected]>
Subject: Re: RFC: Product directory
Date: Mon, 2 Jun 2008 04:33:49 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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 <[email protected]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [email protected]

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

view thread (16+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: RFC: Product directory
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox