Re: Naming Conventions: Table and Columns

From: Manoj A Menon <mmenon@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