Re: Why is MySQL more chosen over PostgreSQL?

On Fri, 2002-08-02 at 13:53, Jeff Davis wrote:
> > Well, if you also have soundcard_products, in your example you could have a
> > product which is both a networkcard AND a soundcard. No way to restrict
> > that a product can be only one 'subclass' at a time... If you can make that
> > restriction using the relational model, you can do the same as with
> > subclasses. But afaict that is very hard to do...
> >
>
> Perhaps I'm mistaken, but it looks to me as if the relational model still
> holds quite cleanly.
>
> CREATE TABLE products (
> id int4 primary key,
> name text );
>
> CREATE TABLE soundcard (
> prod_id int4 REFERENCES products(id),
> some_feature BOOLEAN);
>
> CREATE VIEW soundcard_v AS SELECT * FROM products, soundcard WHERE products.id
> = soundcard.prod_id;
>
> CREATE TABLE networkcard (
> prod_id int4 REFERENCES products(id),
> hundred_base_t BOOLEAN);
>
> CREATE VIEW networkcard_v AS SELECT * FROM products, networkcard WHERE
> products.id = networkcard.prod_id;
>
> Now, to get the networkcard/soundcard combos, you just need to do:
> SELECT * FROM soundcard_v, networkcard_v WHERE soundcard_v.id =
> networkcard_v.id;
>
> For what it's worth, I didn't make any mistakes writing it up the first time.
> It most certainly "fits my brain" well and seems simple and clean.

Yup, you've basically done it -- but you still need the permissions
lines (soundcard people shouldn't be able to modify networkcard products
-- but rules on the views could accomplish that).

create table product(prod_id int4 primary key);
create table networkcard(hundred_base_t boolean) inherits(product);
create table soundcard(some_feature boolean) inherits(product);
create table something(some_feature integer) inherits(product);

My favorite (and regularly abused):

create table package_deal(package_price) inherits (product, networkcard,
soundcard, something);

Poor examples as noone would make a sellable package that way, but it
shows how it is simply shorter to do. New 'product' consists of a
networkcard, soundcard, and something -- always.

Nobody is saying that:

ESC:%s/aba/wo/g

is a real easy way to know to replace all occurrences of 'aba' with
'wo', and there are lots of other ways of doing it -- but if you happen
to know it, then it certainly makes life easier but is not a very
portable command set :)

Views don't do much else but make life easier. Putting the SQL into the
original queries is just as effective and slightly lower overhead.

Inheritance for me makes life a little bit easier in certain places.
It's also easier for the programmers to follow than a wackload of views
and double inserts.