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.
