Re: Table and Field namestyle best practices?

On 11/8/06, novnov <novnovice(at)gmail(dot)com> wrote:
> I am very curious to hear the various conventions folks here have arrived
> at. I don't expect there to be consensus, but the various rationales might
> help me arrive at an approach that works well for me.

Personally I use all lower caps names a typical table might look:

CREATE TABLE names (
name_id serial PRIMARY KEY,
name varchar(100) UNIQUE NOT NULL,
birth date
);
CREATE INDEX names_birth_index ON names (birth)
CREATE INDEX names_name_lower_index ON names (lower(name));
CREATE TABLE winners (
winner_id serial PRIMARY KEY,
name_id integer REFERENCES names
);
CREATE VIEW winner_names_view AS
SELECT * FROM winners JOIN names USING (name_id);

...generally I don't like naming columns like 'id' -- if I put
full names, like name_id then JOIN ... USING(col_id) or
NATURAL JOINs are easy and straightforward.

Sometimes I put a trailing "_view" to mark that given table
is really a view. My index names are composed of
table_col1_col2_index or table_col1_function_index
(like the above lower() case). If index is unique,
I use "_key" as a suffix instead of "_index".

I know couple of people who name their tables like
T_Name, T_Winner etc. (and V_For_Views), but I consider
it a bit superfluous for my tastes. And if I have whole a lot
tables, I like to keep them organized into schemas, which
are powerful beings in PostgreSQL.

Regards,
Dawid