Re: paradigm sanity check needed [long]

Hi,

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)

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.
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
...
Besides, it won’t ever work with the “*” (yeah I know they say “*” is a
bad thing, but I don’t think it is in all situations).

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

What is really needed is an other SELECT option (which could also be
just an extension to “AS”) that will cause the output to have the origin
table name prefixed.

Maybe something like :
SELECT person.* AS person.* FROM person;

Or maybe better...
SELECT * QUALIFIED FROM person;

which could then be :
SELECT person.* QUALIFIED, department.* QUALIFIED FROM person,
department
WHERE person.department = department.id;

OR may be just a GLOBAL option for the entire SELECT, such as :

SELECT QUALIFIED person.*, department.* FROM person, department
WHERE person.department = department.id;

Further name qualification that exteds to the SCHEMA and DATABASE (why
not UNIVERSE? --:) could be
opted with :
SELECT [TABLE|SCHEMA|DATABASE] QUALIFIED * FROM person;

Anyway, I am sure someone else could come up with a better syntax, but
you get the idea ...

Cheers,
Ayhan

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of will trillich
Sent: samedi 1 février 2003 05:28
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] paradigm sanity check needed [long]

i'd like to run our concept up the flagpole and see what kind of
shots go plumb through it--

you know how the standard recommendation is to have every field
across the database with its own unique name? right?

like school_student_certificate.school_student_certificate_year,
long_enough_to_choke_a_horse.long_enough_to_choke_a_horse_thing...

well, we're not doing that. not even close. all the literature
says it's a good idea, but they don't say why. and we want to
find out. :)

here goes--

EVERY table of ours has
*.id serial primary key,
*.created date default current_date,
*.modified timestamp(0),
*.by references person(id),

and when one table links to another (aside from the "edited by
field above, that is) the name of the foreign key matches that of
the table whose id we're after:

team.person => person.id
team.org => org.id
dates.date_types => date_types.id
loc.state => state.id
loc.nation => nation.id
project.org => org.id
...etc...

programmatically -- at first glance -- this looks like a DREAM to
manage. (this is how we know there must be something seriously
wrong with it. red alert!)

using this setup, we can easily link up, using sql, which tables
refer (defer) to others -- detecting a parent/child relationship
is a snap:

-- here's the main "subroutine" used below:
CREATE VIEW sys_tables as
SELECT
c.relname AS class,
oid AS c_oid
FROM
pg_class c
WHERE
c.relname !~ '^pg_' -- not a postgres system table
AND
c.relname !~ '^_' -- not a table-behind-a-view
AND
c.relkind IN ('v','r') -- view or relation/table
ORDER BY
c.relname
;

COMMENT ON COLUMN sys_tables.class is 'System Table (class)
name';

then once we specify our lookup/validation table pattern, we can
figure out the rest:
-- linking tables (for many-to-many)
-- subtables (many-to-one)
-- data tables (the nacells of the enterprise)

for the validation/lookup tables, they're all named *_TYPE -- and
then there's the other two: "state" and "nation"; we have some
special-case hard-coding for those two:

-- VALIDATION TABLES (having *_TYPE names)
CREATE VIEW sys_lookup_tables as
SELECT
t.class
FROM
sys_tables t
WHERE
(
t.class ~ '_type$'
) OR (
-- don't forget these!
t.class IN ('state','nation') -- special cases
)
ORDER BY
t.class
;

COMMENT ON COLUMN sys_lookup_tables.class is 'Name of
verification/lookup table';

if we eventually add others, this is the ONE place we'd have to
mention them. which normal form is THAT? :)

this next view shows ALL subsidiary relationships, both subtables
and linking (many-to-many) tables:

-- SUB TABLES (TABLES having field names matching other table
names)
CREATE VIEW sys_sub_tables as
SELECT
s.class as sub,
p.class as parent
FROM
pg_attribute a,
sys_tables s,
sys_tables p
WHERE
a.attrelid=s.c_oid -- field from Sub
AND
a.attname=p.class -- matches table Parent
ORDER BY
s.class,
p.class
;

COMMENT ON COLUMN sys_sub_tables.sub is 'Subsidiary table
name';
COMMENT ON COLUMN sys_sub_tables.parent is 'Parent table name';

and here, we find the linking tables:

CREATE VIEW sys_link_tables as
SELECT
s.sub as class
FROM
sys_sub_tables s
left join
sys_lookup_tables l
on (l.class = s.parent)
WHERE -- they're subtables, but not lookup tables
l.class is null
GROUP BY
s.sub
HAVING
count(*) > 1
;

COMMENT ON COLUMN sys_link_tables.class is 'Name of linking
(many-to-many) table';

(explain select * from sys_link_tables -- quite amusing!)

pulling field descriptors out (comment) is pretty easy, too.
(internationalization is a long way off, of course...)

what do y'all think? "man the lifeboats"? should we be expecting
sea water in the scuppers anytime soon? and why (or why not)...

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

will(at)serensoft(dot)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/ !

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html