Re: "user" - Mailing list pgsql-general

Marshall Spight wrote:
> "Daniel Åkerud" <zilch@home.se> wrote in message
> news:007101c13b96$834e8960$c901a8c0@automatic100...
> >
> > Anyway I called it "uzer" now. Maybe usr is better... users is out of the
> > question since table names are supposed to go in singularis.
>
> Since a table is a collection of things, plural always seemed to make
> more sense to me. But I've seen it done both ways. Postgres system
> tables are singular, but it SqlServer, they're plural.
>
> What do other people think?

hmm -- here's what we do. with postgresql 7.1, we use

    static lookups: PLURALS
        states.*
        colors.*
        types.*
        stages.*
        codes.*
    (the collection of records -- plural -- in these tables
    define and restrict what options are available for certain
    contexts.)

    fluctuating data: SINGULARS
        client.*
        contact.*
        property.*
        image.*
        message.*
    (each record -- singular -- in these tables represents a
    significant thing that's of direct importance to our bottom
    line.)

plus, the actual tables (which are prefixed with "_") are hidden
by the views (sans leading "_") we use to access them:

    create TABLE _cust ( ... );
    create VIEW cust AS SELECT ... FROM _cust ...;
    create TABLE _contact ( ... );
    create VIEW client AS SELECT ... FROM _cust,_contact WHERE ...;
    create RULE add_client AS ON INSERT TO client DO INSTEAD ...;
    create RULE upd_client AS ON UPDATE TO client DO INSTEAD ...;

and for joining via serial indexes, we use "ID":

    create table _colors (
        id SERIAL,
        ...
    );
    create table _stages (
        id SERIAL,
        ...
    );
    create table _gizmo (
        ...
        colors_id INTEGER REFERENCES _colors( id ),
        stages_id INTEGER REFERENCES _stages( id ),
        ...
    );

so that any field <something>_id references _<something>.id (we
can't use natural joins, but that's not a big deal with us.)
alas, there is a weakness here: if there's more than one instance
of a particular subtable: colors_id might need to be
interior_colors_id and exterior_colors_id. zut alors!

for name collisions, we improvise: instead of "user" we use "who"
(after considering, and rejecting, "human" "person" "someone"
and "creature") for example.

so, what pre-existing standards are there, Out There in Database
Land? and what problems can you postgresql fans find with this
paradigm? (it's worked pretty well for us so far... :)

--
They don't remember whether or not they weren't doing anything
I didn't want them to do.
    -- Karen, on why she feeds the cats when they misbehave

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