Thanks that some good real world input. Not sure what it'll add up to for me yet but good reference points. In the db centric world I've been inhabiting for these years there are many conventions re namestyles, they extend to table names, query names, field names, variables, everything. I started out disliking the schemes but over time saw the sense and adopted or munged for my own taste. Unless some kind of prefixing is incorporated into naming conventions, a name like employee could be a table, a query, an 'object', a field, etc. All of my prev work is very easy to read because all names are clearly pegged. tblOrganization is a regular data table, tlkpCity is a lookup table. qryOrganization is a select query (usually by convention tblOrganization.*, ie all records), qappEmployeeSalary is an append query, qdelEmployeeSalary a delete query (usually with params). Also, when refactoring is needed, and table/field etc needs to be renamed, having unique names is pretty cool...and the patterns I've built up name items in families (like the Org field name prefixes) so sometimes one can swat a bunch of name changes at once...carefully. These conventions have helped me navigate databases when they start getting a signficant number of objects in them. I know many do without, but they've been very good for me. I don't need to repro the naming conventions I've built up over the years, but would like to keep the utility they offer somehow. 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#a7245644 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
