Re: Table and Field namestyle best practices?

Re this part of what you wrote:

"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."

Rereading it I'd like to clarify my current approach.

The reason a fully qualified pointer (ala "creator_user_id") is cool is that
it's obvious what it's about in comparison with another field
"modified_user_id". One points to the creator and the other to the most
recent modifier of the record.

Following your path to this kind of clarity one could name the fields
"creator_id" and "modifier_id". Those are ok but in more complex schemas
(esp ones that are not 'common' objects like "user") it might not be so
obvious that the 'creator' part is an alias for 'user'. The approach I've
taken so far makes it obvious but is more verbose of course.

Not saying my legacy approach is better than your suggestion, but I did want
to note the rationale for the pattern I arrived at ages ago. A decision to
simplify in pgsql by using all lowercase forces me to rethink my entire
namestyle practice...probably only seasoned devs can understand how weird
that is to consider.

Alexander Staubo wrote:
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

--
View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7299349
Sent from the PostgreSQL - general mailing list archive at Nabble.com.