Re: CREATE TABLE with REFERENCE

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