Re: CREATE TABLE with REFERENCE

Interesting.  That might actually be doable.

Thanks!

Jon

On Tue, 29 Jul 2003, Dmitry Tkach wrote:

> Jonathan Bartlett wrote:
>
> >>Not *one* table. I never advocated that. It is perfectly normal to split
> >>your data into different tables *vertically* (i.e. things that do not
> >>have any intersection between their data, should go into different
> >>tables), but it very rarely (if at all) makes any sense to split it
> >>*horizontally* (so that identical columns sit in different tables, just
> >>
> >>
> >
> >Okay, so I shouldn't merge the tables then.  Let me show you my schema:
> >
> >Sponsor -> object_id, name, url, representatvie (points to rep table),
> >city (points to city table), primary contact (points to contact table),
> >active
> >
> >Payments -> object_id, sponsor (points to sponsor table), when_paid,
> >payment_type, payer_contact (points to contact table), company address
> >(points to addresses table), billing address (points to addresses table),
> >CC Info (I won't spell it all out for you), amount
> >
> >Notes -> object_id, noted_object (points to ANY table), note_title,
> >note_text, note_creation_date, not_creator(points to user table), active
> >
> >So, since Notes can be attached to any table, I don't see how you are
> >saying I should combine them, except to combine EVERYTHING into a single
> >table, and have a value at the beginning to use as the record "type".
> >
> >
> Well... this is not the ideal solution (ideally, your
> contacts/reps/addresses/cities would need to be rethought somewhat too),
> but something you could use as an illustration of what I am talking about...
>
> create table entity
> (
>    id serial primary key,
>    name     text not null,
>    contact  int not null references contacts,
>    address int not null references addresses,
>    active boolean,
>    insertstamp timestamp
> );
>
> create table sponsor
> (
>     id  int primary key references entity,
>     rep int references rep,
>     url  text
> );
>
> create table payment
> (
>      id int primary key references entity,
>      sponsor int references sponsor,
>      type int,
>      billing_address int references addresses,
>      not_spelled_out_info text
> );
>
> create table notes
> (
>     if int primary key references entity,
>     object_id int  not null references entity,
>     title text,
>     body text
> );
>
> ... to be really thorough, the entity should also have something like
> object_type on it, that should be included into the FKs, to make sure
> you cannot create, say, a payment, and a sponsor with the same id...
> Also, contacts/reps/users, should be in the same table (linked to
> entity) as well ... etc...
>
> But, as I said, this seems to be a fairly clear illustration of the
> approach...
>
> Dima
>
>
>
>
>
>
>
>
>
> >
> >
> >>No. They would have a base class of "Object" (or whatever), and the
> >>'notes' would be linked to the Object.
> >>This would in fact, be a *beatiful* solution... it's a shame really that
> >>it doesn't work.
> >>
> >>
> >
> >As I said, the tool is limitted.
> >
> >Jon
> >
> >
>
>
>