Naming Conventions: Table and Columns
Obnoxio The Clown
From: Manoj A Menon <mme...@apis.dhl.com>
>
>Let me explain what exactly I am after:
>"The Table and Column Naming Guidelines/Recommendations"
>
>I am looking for some table and column naming guidelines for Informix
>tables - there are some well-known "standards" which say that "use the
>same column name for the same purpose" - which would result say
>employee_number for all tables wherever employee_number is referred to.
>There has been some rebuuttal from the programmers that this causes
>confusion while testing/debugging and to the administrators since they
>need to understand the exact table relationships - basically decrease
>productivity - is this true ?. The system in question would have 28
>tables and approx 350 columns - if the above guideline is followed the
>development group reckons that 231 columns would be duplicated - e.g if
>emp_id is a column name to represent employee id - this would be present
>in 18 tables as emp_id, dept_id would be present in another 12 tables -
>so there would be 12 instances of dept_id and 18 instances of em_id in
>an SQL statement which uses all related tables using joins - the
>reckoning is that the resultant SQL statement is not easily readable -
>instead each column name is prefixed with a 2 character table name e.g
>dept_emp_id.
Oh. How about referring to "dept.emp_id"?
>So...I wanted to ask this group of the opinion and refer to me to any
>existing standards/guidelines
>
>Say if we had a group of logical data elements for employee and another
>for it's relation with the department - what would the column
>naming conventions or guidelines are. For e.g. employee tables can be
>named as "employee" or "employee_details" or "employee_recs"
>or "employee_det" and so on - is there a standard or a recommended
>practice.
I recommend as full a name as possible. "employee" would be my choice for
the table above. Avoid the temptation to use some stupid prefix like o_ to
refer to operational data.
>Extending this situation to the column names will the Employee Number be
>named as
>no or emp_no or emp_id or id or employee_number or employee_num or
>empno or empid or employeenumber or employeenum or
>emp_number and so on - is there a convention or guidelines on the column
>naming.
I would use _key to identify a key, so you would have employee.employee_key.
I generally like to make the name as clear as possible and avoid having
suffixes to identify data type. So birthdate rather than birth_date,
telephone_number rather than phone_no, etc.
>Similarly, if the same attribute appears as a foreign key in the
>employee-department table, what would the column name for employee
>number be in this table.
employee_key?
>Assume, we have
>
>CREATE TABLE employee (
>emp_id int, emp_fname char (64), emp_lname (64) ) ;
>
>CREATE TABLE department (
>dept_id char (6), dept_division char (6) ) ;
>
>CREATE TABLE employee_timelog
>( emp_id, dept_id, emp_hours number) ;
>
>
>Now, it may be aware that the above columns have some table-prefixes
>added - is this recommended or not.
Eh?
>Similarly, if emp_id is a foreign key, how would the column name look
>like in the employee_timelog table - will be emp_id or say
>time_log_emp_id etc etc.)
emp_id
>(assuming an employee can float over a department )
>
>I am sure you might have got a gist what exactly I am after - if not I
>will try to clarify further.
It *is* a religious issue. There are a zillion opinions, and they're
probably all equally justifiable. Just try and think it through before you
settle on anything. If you ever need to make a fudge to get things to work,
it's not a good convention.
_____________________________________________________________________________________
Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
Robert Stuart
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.
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
Have a look at SQL-92
-----Original Message-----
From: Manoj A Menon [mailto:mme...@apis.dhl.com]
Sent: 22 November 2000 11:12
To: inform...@iiug.org
Subject: Naming Conventions: Table and Columns
All,
Let me explain what exactly I am after:
"The Table and Column Naming Guidelines/Recommendations"
I am looking for some table and column naming guidelines for Informix
tables - there are some well-known "standards" which say that "use the
same column name for the same purpose" -
[deleted]
Assume, we have
CREATE TABLE employee (
emp_id int, emp_fname char (64), emp_lname (64) ) ;
CREATE TABLE department (
dept_id char (6), dept_division char (6) ) ;
CREATE TABLE employee_timelog
( emp_id, dept_id, emp_hours number) ;
[deleted]
Surfer!
<Robert...@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.
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!
It's also worth making sure all table names are either singular or
plural - it's confusing to have a system where there's a mixture. Here
speaks experience....
Quite a few packages which access database schemes and the 'do things'
with them require that joined columns share the identical name.
>
>
>
>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
>
>
>Have a look at SQL-92
Ah! Confirming my comments above, though I was really thinking about PC
packages drawing pretty pictures of the schema.
>
>
>-----Original Message-----
>From: Manoj A Menon [mailto:mme...@apis.dhl.com]
>Sent: 22 November 2000 11:12
>To: inform...@iiug.org
>Subject: Naming Conventions: Table and Columns
>
>
>All,
>
>Let me explain what exactly I am after:
>"The Table and Column Naming Guidelines/Recommendations"
>
>I am looking for some table and column naming guidelines for Informix
>tables - there are some well-known "standards" which say that "use the
>same column name for the same purpose" -
>
>[deleted]
>
>Assume, we have
>
>CREATE TABLE employee (
>emp_id int, emp_fname char (64), emp_lname (64) ) ;
>
>CREATE TABLE department (
>dept_id char (6), dept_division char (6) ) ;
>
>CREATE TABLE employee_timelog
>( emp_id, dept_id, emp_hours number) ;
>
>
>[deleted]
Surfer! Send email to: surfer at
nevis-view dot
demon dot co dot uk
"I can resist anything but temptation" - Oscar Wild ;-)
Robert Stuart
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.
In earlier versions of i4gl this style (though not this particular query) is
unpredictable. (I believe that '@' can be used in this case - it always
suppress me when I find it in code).
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.
(I have problems with fname and lname, but that's for another thread.)
Personally, I prefer not to use prefixes - I tend to run short of my 20
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.
I do take your point about external packages guessing about joins. Some
packages also check referential integrity. I've seen MS Access trace
referential integrity through (otherwise uncalled) intermediary tables with
differently named reference columns. The only clues to the path were the
referential links. (There were no circular links in that database!)
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.)
Surfer!
<Robert...@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.
My only disagreement with the above is that emp_ has been used as a
prefix in two tables.
>In earlier versions of i4gl this style (though not this particular query) is
>unpredictable. (I believe that '@' can be used in this case - it always
>suppress me when I find it in code).
I suspect the problem only occurs where there is a program variable with
the same name as a database column, which is when using the '@' makes it
clear to the underlying stuff what is really meant. However I don't
duplicate column names in program variables so don't have that problem.
>
>
>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.
>(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
>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.
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. My fingers are shorter than they
used to be - but that's probably 'usenet' instead of over-long column
names! However shorter names are also easier to remember, which is also
an asset, and there is less chance of mis-typing or mis-spelling them.
'vi' doesn't include a spell checker!
Jonathan Leffler
On Thu, 23 Nov 2000, Surfer! wrote:
>Robert Stuart <Robert...@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...@nospam.demon.co.uk]
>>Sent: 22 November 2000 19:30
>>Subject: Re: Naming Conventions: Table and Columns
>>
>>Robert Stuart <Robert...@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 table> <right paren>
<cross join> ::=
<table reference> CROSS JOIN <table reference>
<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]
<join type> ::=
INNER
| <outer join type> [ OUTER ]
| UNION
<outer join type> ::=
LEFT
| RIGHT
| FULL
<join specification> ::=
<join condition>
| <named columns join>
<join condition> ::=
ON <search condition>
<named columns join> ::=
USING <left paren> <join column list> <right paren>
<join column list> ::=
<column name list>
>>>-----Original Message-----
>>>From: Manoj A Menon [mailto:mme...@apis.dhl.com]
>>>Sent: 22 November 2000 11:12
>>>
>>>Let me explain what exactly I am after:
>>>"The Table and Column Naming Guidelines/Recommendations"
>>>
>>>I am looking for some table and column naming guidelines for Informix
>>>tables - there are some well-known "standards" which say that "use the
>>>same column name for the same purpose" -
>>>
>>>[deleted]
>>>
>>>Assume, we have
>>>
>>>CREATE TABLE employee (
>>>emp_id int, emp_fname char (64), emp_lname (64) ) ;
>>>
>>>CREATE TABLE department (
>>>dept_id char (6), dept_division char (6) ) ;
>>>
>>>CREATE TABLE employee_timelog
>>>( emp_id, dept_id, emp_hours number) ;
>>>
>>>[deleted]
--
Yours,
Jonathan Leffler (Jonathan...@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"