Re: paradigm sanity check needed [long] - Mailing list pgsql-general

discussion on using this field-naming mechanism:

    TOPTABLE:        UNDERTABLE:
       id -- pkey       id -- pkey
       data             data
       otherdata        DATTABLE -- points to toptable.id

instead of the usual undertable.undertable_id and
undertable.toptable_id --

On Sat, Feb 01, 2003 at 04:40:39PM +0100, Ayhan Ulusoy wrote:
> I played around with a similar “paradigm shift” for some
> time (except for the lookup tables, which I prefer to keep
> seperate).
>
> It’s true that it would be ALICE IN WONDERLAND -- only if it
> were practical.
>
> what we hit against is SQL syntax... (just syntax, not
> conceptual clash)

hmm. show me what you mean--

> Now, SQL  lets you spell out your fields when you use them,
> such as : person.id, person.created, ...  That’s a good
> thing.
>
> BUT, the column names that are OUTPUT from a SELECT don’t
> have the table name prefixed.  Which can be a good or a bad
> thing, depending...
>
> Consider a query like this:
> SELECT * FROM person;
>
> The column names you will get out of this will not have the
> table name prefixed.

i don't yet see the evil there. YOU specified which table, so you
ALREADY KNOW what the table is.

> Even the following won’t work as you sometimes wished it did:
>
> SELECT person.id, person.created FROM person;
>
> It is on the otherhand possible to use “AS” with each and
> every column name :
>
> SELECT person.id AS person.id , person.created AS
> person.created FROM person;
>
> That should have the prefixes in... What a waste of finger
> energy though ...

why is it crucial to have the table.* prefix if you're selecting
from one table?

> When you have JOINS, it’s even worse...

so your objection is primarily in the joins, then:

    select
        project.id, org.id -- error: can't result in two "id" fields!
    from
        project,
        org
    where
        project.org = org.id
    ;

hmm, yes. badness that way. i can see doing

    select
        -- other fields,
        project.id AS PROJECT_ID,
        org.id     AS ORG_ID
        -- "wasted^H^H^H^H^H^Hinvested finger effort"
    from
        project,
        org
    where
        project.org = org.id
    ;

to get a list of ALL the projects and all their related
enterprises; then we just split/_/ to get table.field back.

but more commonly we will want projects for a certain
institution, so we'll already know the org.id--

    select
        -- other fields,
        project.id
    from
        project,
        org
    where
        project.org = org.id
    AND
        ORG.ID = $THIS_VALUE
    ;

here since we know which org we're after, we might only be
interested in the project id's related to it. is there
other nefariousity lurking in there somewhere?

i can also see creating views for each "these_from_those"
relation which would keep that part of the logic out of the
middle-layer of the application:

    create view project_from_org as
    select
        p.*
    from
        project p,
        org     o
    where
        o.id = p.org
    ;
    select * from project_from_org where org=$some_org_id;

and if we somehow forget which org this was, we can look in the
resulting "org" field pulled from the project table.

===

the drawback i see in using

    ORG                PROJECT
       org_id             project_id
       org_data           org_id

is that you're just about going to have to hard-code every
crosslink (we *know* project.org_id links to org.org_id) or do a
bunch of split/_/ anyhow (when fieldname ends "_id" find table
mentioned in before underscore then refer to its field of the
same name). no?

in some ways it's six-of-one-and-a-half-dozeon-of-the-other.
but in other ways the "tablename.linkedtable_id" approach seems a
hair more tedious than "tablename.linkedtable [as id]" one.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !