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