Re: Table and Field namestyle best practices?

On 11/8/06, novnov <novnovice@gmail.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