Re: Naming Conventions: Table and Columns

On Thu, 23 Nov 2000, Surfer! wrote: >Robert Stuart <Robert.Stuart@nfer-nelson.co.uk> writes >>Surfer, >>I'm not sure where, or if, you disagree with me. >> >>I would argue that... >> >> SELECT emp_fname, >> emp_lname, >> dept_division, >> emp_hours>> >> FROM employee, department, employee_timelog >> >> WHERE employee.emp_id = employee_timelog.emp_id >> AND department.dept_id = employee_timelog.dept_id >> >>... is poor style. It is poor style in my view too... >My only disagreement with the above is that emp_ has been used as a >prefix in two tables. My preference, FWIW (probably nothing since it just muddies what had been clear waters), is to use the same name to reference the same logical attribute in each table, except when there are two distinct uses for the logical attribute. So, I'd be quite happy with using emp_id for the Employee ID column in every table where the Employee ID appears, except in the Management Hierarchy table where the two attributes are Managed Employee ID and Manager Employee ID, so I might use managed_emp_id and manager_emp_id in that table. That being the case, I'd write your sample SELECT as: SELECT E.Fname, E.Lname, D.Division, T.Hours FROM Employee E, Department D, Employee_timelog T WHERE E.Emp_id = T.Emp_id AND D.Dept_id = T.Dept_id I use the single letter table aliases uniformly throughout, and expect the joining columns in tables to have the same name, in general. >>[...] >>If you do use a prefix convention, then, *if at all possible*, 'emp_' should >>be used in one table, and one table only. It is not clear from the query >>above that emp_hours comes from employee_timelog, while emp_fname and >>emp_lname come from employee. This is the trouble with prefixes; the other is how you cross reference the emp_id column in the employee timelog table - tlog_emp_id? This can get out of hand if you have a hierarchy of tables tdet_tlog_emp_id for the employee_timelog_detail table, for example? >>(I have problems with fname and lname, but that's for another thread.) > >As I originally posted, I would have used empl_ as the prefix for the >hours column, and others in that table. > >>Personally, I prefer not to use prefixes - I tend to run short of my 20 18 in pre-9.20 systems; 128 in 9.20 and 9.21 (at the moment; expect the 9.x versions to acquire long names at some point). >>character limit on column lengths (what's the new limit?). Where two or >>more columns have the same name, they should have the similar use and >>meaning, and be clear from context how they differ. > >However prefixes really help people who are new to a database get the >idea about queries without having to look in the schema for which table >each column is in. We have always managed with the 18-character >limitation and have rarely got anywhere near it. Of course, if you always prefix every column name in every SELECT statement with an alias, then it is unambiguous which table the column comes from. And the 9.x DISTINCT types can be useful in describing the relationships in a database (along with the explicit referential integrity constraints). You can create a distinct types for Employee ID, and then use that in every table where there is one or more Employee ID columns. The types (domains) make it clear that these columns are the same types, and are therefore candidates for joining. I'm not sure how distinct types interact with SERIAL columns (probably badly), which may reduce their usefulness. >IMHO column names need to be a balance between being very clear and >hence very long to type, and so abbreviated that they are unclear. I >try to keep them to 12 chars or less. [...] Agreed, and a reasonable rule of thumb. >>I do agree with making table names either singular or plural, but not >>with mixing styles. My personal preference is to use singular names >>(usually shorter, and I'm still stuck with only 18 characters). There >>is an argument for using singular names in tables with one row only >>(configuration tables), and plurals for all others. (I don't agree >>with the argument, but it's there.) In general, I find singular better than plural, but it is not always so. >>-----Original Message----- >>From: Surfer! [mailto:nevis-view@nospam.demon.co.uk] >>Sent: 22 November 2000 19:30 >>Subject: Re: Naming Conventions: Table and Columns >> >>Robert Stuart <Robert.Stuart@nfer-nelson.co.uk> writes >>> >>>Yeah >>> >>>It's called lazy. >>> >>>I personally feel that ... >>> >>> SELECT employee.emp_fname, >>> employee.emp_lname, >>> department.dept_division, >>> employee_timelog.emp_hours>>> >>> FROM employee, department, employee_timelog >>> >>> WHERE employee.emp_id = employee_timelog.emp_id >>> AND department.dept_id = employee_timelog.dept_id >>> >>>... is unambiguous - and to some extent - self commenting. And verbose. Use table aliases! >>I use a different prefix for column names in each table except for >>foreign keys (such as employee_timelog.emp_id in the above example), so >>I would use employee_timelog.empl_hours, not emp_hours. After all >>emp_hours is certainly not a foreign key! If you are going to use prefixes, being systematic about it is critical. Nothing is worse than having to go guessing the column names the whole time because everything is ad hoc. >>[...] >>Quite a few packages which access database schemes and the 'do things' >>with them require that joined columns share the identical name. And those packages cannot manage things like the Management Hierarchy table I outlined earlier. >>>If I recall correctly, SQL-92 has a concept of natural joins, where >>>something like ... >>> >>> SELECT employee.emp_fname, >>> employee.emp_lname, >>> department.dept_division, >>> employee_timelog.emp_hours >>> JOINING employee, department, employee_timelog>>> >>> ... would return the same result - The join is on fields with the same name The notation appears in the FROM clause, is parenthesized, and probably needs an alias. Here's some BNF for the FROM clause: <from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] <correlation specification> ::= [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] <table reference> ::= <table name> | <table name> <correlation specification> | <derived table> | <derived table> <correlation specification> | <joined table> <derived column list> ::= <column name list> <derived table> ::= <table subquery> <table subquery> ::= <subquery> <joined table> ::= <cross join> | <qualified join> | <left paren> <joined