Subject: RFC: Product directory

From: "Dave Page" <[email-masked]>
Date: Mon, 2 Jun 2008 09:30:58 +0100

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)
licence char(1) -- Licence type flag

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?

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

---

From: David Fetter <[email-masked]>
Date: Mon, 2 Jun 2008 04:33:49 -0700

> 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;

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

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

---

From: "Dave Page" <[email-masked]>
Date: Mon, 2 Jun 2008 12:58:49 +0100

> issues.  Pointing to a web site, where appropriate, would handle this

Agreed but a) we already display that data and b) it's useful to give
users a ballpark figure, even if something like "$99.99US as at
15/12/2007"

<snip ddl>

> Products may have more than one category.

We don't currently allow listings in more than one category, and I'm
inclined to continue with that (unwritten) policy - the reason being
that listing in multiple categories will easily bloat the number of
generated pages that a product might be listed in, potentially
affecting search engine rankings. With relatively generic categories I
don't see any real need to allow multiple categories.

>  and more than one license.

Again, not sure I see a need. Either it's commercial, OSS, or freeware
- I don't think there's much scope to have more than one (the obvious
exception is something like "$99.99, or free to educational users" but
I'd just class that as commercial). I don't want to list the actual
OSS licence used as folks can decide whether a product meets with
their personal ethics once they visit it's homepage.

> "publisher" out into a separate table?

Possibly. Makes the coding & management a little more tricky though.
If we were to do that perhaps it should be part of a larger project to
have a directory of vendors/publishers etc for news, events, services
and products.

---

From: "Marc G. Fournier" <[email-masked]>
Date: Mon, 02 Jun 2008 09:19:01 -0300

> issues.  Pointing to a web site, where appropriate, would handle this

Agreed .. would just listed it as 'OSS, shareware vs commercial' ...

- -- 
Marc G. Fournier        Hub.Org Hosting Solutions S.A. (http://www.hub.org)
Email . [email-masked]                              MSN . [email-masked]
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

---

From: "Joshua D. Drake" <[email-masked]>
Date: Mon, 02 Jun 2008 08:26:42 -0700

> licence char(1) -- Licence type flag

I would push publisher and publisher_url to their own table. Further we 
may really want to consider that a publisher could be a sponsor. We 
don't want to duplicate all this info.

Sincerely,

Joshua D. Drake

---

From: "Joshua D. Drake" <[email-masked]>
Date: Mon, 02 Jun 2008 08:28:52 -0700

> users a ballpark figure, even if something like "$99.99US as at

I have to go with fetter here. Pricing is not our concern.

> their personal ethics once they visit it's homepage.

+1

> have a directory of vendors/publishers etc for news, events, services

Yeah I mentioned this in my previous post. Publisher really needs to be 
pushed out. There is entirely too much redundant information that can be 
accumulated.

Sincerely,

Joshua D. Drake

---

From: "Dave Page" <[email-masked]>
Date: Mon, 2 Jun 2008 16:46:57 +0100

> I have to go with fetter here. Pricing is not our concern.

I actually feel quite strongly about that one - it's not *our*
concern, but it is the concern of the users. There's nothing worse
than researching a product, and eventually finding out after trawling
the website for half an hour that it's way too expensive. As an
end-user I always wanted to see a ballpark figure up front.

> pushed out. There is entirely too much redundant information that can be

OK so the difficulty here/previously is that you are essentially
saying I need to write a publisher management system and a product
management system, and update sponsors, news, events, services etc to
use that data as well. Nice in theory, but not something I have time
to do.

---

From: Andrew Sullivan <[email-masked]>
Date: Mon, 2 Jun 2008 11:48:49 -0400

On Mon, Jun 02, 2008 at 12:58:49PM +0100, Dave Page wrote:
> I'd just class that as commercial). 

Seems like you need a "multiple" class, then.  (Dual licenses are
pretty common.)

A
-- 
Andrew Sullivan
[email-masked]
+1 503 667 4564 x104
http://www.commandprompt.com/

---

From: "Joshua D. Drake" <[email-masked]>
Date: Mon, 02 Jun 2008 08:56:34 -0700

> end-user I always wanted to see a ballpark figure up front.

And who is going to make sure the pricing is up to date? (hint: no one
will)

> use that data as well. Nice in theory, but not something I have time

But I am sure you have time to create a secondary table and the code for
it. I am not asking you to create all the code for sponsors, news,
events. I am asking you to use a reusable design so others can create
code for those services.

Sincerely,

Joshua D. Drake

---

From: "Dave Page" <[email-masked]>
Date: Mon, 2 Jun 2008 17:02:23 +0100

> And who is going to make sure the pricing is up to date? (hint: no one

I'm guessing you didn't read all of the thread?

> events. I am asking you to use a reusable design so others can create

That I can do. It sounded like you wanted me to get rid of all the
redundant info at the same time.

---

From: "Joshua D. Drake" <[email-masked]>
Date: Mon, 02 Jun 2008 09:06:53 -0700

> I'm guessing you didn't read all of the thread?

I may have missed that. Let me review again.

> redundant info at the same time.

:)

Sincerely,

Joshua D. Drake

---

From: "Joshua D. Drake" <[email-masked]>
Date: Mon, 02 Jun 2008 09:23:13 -0700

> I'm guessing you didn't read all of the thread?

Dave made a good point to me on Jabber about the pricing issue.
Basically, I don't buy software so this issue doesn't quite sit right
with me. He is right, I license very little commercial software for
business purposes. In fact the *only* commercial software CMD has
purchased is QuickBooks. So I am going to drop my argument against this.

Sincerely,

Joshua D. Drake

---

From: [email-masked]
Date: Mon, 2 Jun 2008 19:35:13 +0100

Please excuse the top-post (limitation of Jesus phone + gmail)

Thinking about this on the drive home, it seem a price range would
give the user an indication of whether to consider a package further,
and be relatively maintenance-free. E.g.

$0.00 - $99.99
$100.00 - $249.99
$250.00 - $499.99
$500.00 - $999.99
$1000.00 - $4999.99
$5000.00+

---

From: "Selena Deckelmann" <[email-masked]>
Date: Mon, 2 Jun 2008 13:45:09 -0700

> and be relatively maintenance-free. E.g.

I purchase software on a regular basis, and my buckets are something more like:

0-250
251-1000
1001-10000
10k+

And really, a useful categorization for filtering that speaks to both
cost and use case would be:

General Public
Education
Small to Medium-Sized business (individual to ~100 employees)
Enterprise

-selena

-- 
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

---

From: Josh Berkus <[email-masked]>
Date: Mon, 2 Jun 2008 13:55:49 -0700

Dave, All:

1) I think this is a good idea.  Thanks, Dave.

2) I don't think we should deal with price at all.  It's too complicated. 
Imagine a product which is $500 retail, $3000 enterprise and $27.50 OEM.

3) Josh is right that we'll eventually want this to be linked to the 
sponsors list.  So we should prepare for that somehow.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---

From: Robert Treat <[email-masked]>
Date: Mon, 02 Jun 2008 17:25:26 -0400

> Imagine a product which is $500 retail, $3000 enterprise and $27.50 OEM.

people want the pricing information, so i think usefullness is not the issue, 
its how to capture non-standard input. I say punt it and just give a text box 
for pricing.  This would give the same level of information as what is 
available now. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL