Re: Table and Field namestyle best practices? - Mailing list pgsql-general

On Nov 8, 2006, at 18:49 , novnov wrote:

> I've been using namestyles with mixed case like OrgID. That is much
> more
> readable than orgid. Probably a good convention to adopt would be
> to use
> namestyles like org_id. That change I can live with.

Both are perfectly acceptable, though the mixed-case version has
drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers
something of a second-rate citizen; "orgid" and "OrgID" are both
going to be resolved to the same object, unless you explicitly double-
quote it. Ambiguity is rarely a problem, but because there are no
checks for consistency, inconsistencies tend to sneak in, especially
in team projects; some people might type "OrgID", some "OrgId", and
so on.

Note that lower-case, underscore-delimited variable identifiers are
consistent with mainstream coding conventions for C, C++, Ruby,
Python and most languages in the Unix world (Java and JavaScript
being notable exceptions). After several years of trying to go
against the grain and be consistent across the board, I ended up
giving in and always using whatever is appropriate in the language/
environment I work in.

> But another issue is the way that I've been naming foreign key
> references. I
> tend to prefix field names with a table based acronym. So names like
> OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So,
> if I have
> a table tblEmployee, fk to the org table would be like EmpOrgID. I
> know many
> would simply use OrgID as the fk in tblEmployee, but I have liked
> compounding the names because is results in completely unambiguous
> field
> names throughout the db. If I'm giving up the mixed case naming, I
> could use
> emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best.

For column names, I recommend using whatever is natural in the
decribing a field, irrespective of what the field is actually
pointing towards. For example, a table representing an object with a
creator attribute pointing to a user would have a column "creator_id"
referencing "users (id)" -- not, say, "creator_user_id", which is
superfluous. The id suffix is there to tell me it's an identifier,
not the creator proper.

In your case, in the table "organizations" the column names would be
"id", "name", "city" and so on, and a table "employees" would have a
column "organization_id" with a foreign-key reference. This simple
convention translates more easily to one's mental model of a query
such as "select all employees where the organization name is 'foo'",
which becomes:

   select * from employees
   join organizations on id = organization_id
   where organizations.name = 'foo'

as opposed to

   select * from tblEmployees
   join Org on OrgId = EmpOrgId
   where Org.OrgName = 'foo'

or something.

I am curious as to why you need to prefix table names with "tbl" in
the first place.

Alexander.