Naming Conventions: Table and Columns

This is a multi-part message in MIME format. --------------CB0146E5B98F55C45872122A Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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" - 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. 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. 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. 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. 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. 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.) (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. Looking forward to your responses. Thanks and Rgds, Manoj --------------CB0146E5B98F55C45872122A Content-Type: text/x-vcard; charset=us-ascii; name="mmenon.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Manoj A Menon Content-Disposition: attachment; filename="mmenon.vcf" begin:vcard n:Menon;Manoj Arvindakshan x-mozilla-html:FALSE org:Business Systems Development;Infrastructure Database Systems adr:;;;;;; version:2.1 email;internet:mmenon@apis.dhl.com title:Group Manager fn:Manoj A Menon end:vcard --------------CB0146E5B98F55C45872122A--