RE: Naming Conventions: Table and Columns

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.) -----Original Message----- From: Surfer! [mailto:nevis-view@nospam.demon.co.uk] Sent: 22 November 2000 19:30 To: informix-list@iiug.org Subject: Re: Naming Conventions: Table and Columns In article <8vgpm5$775$1@news.xmission.com>, 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. 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:mmenon@apis.dhl.com] >Sent: 22 November 2000 11:12 >To: informix-list@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 ;-)

In article <8viqnf$qrj$1@news.xmission.com>, 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. 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! > >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.) > > > > >-----Original Message----- >From: Surfer! [mailto:nevis-view@nospam.demon.co.uk] >Sent: 22 November 2000 19:30 >To: informix-list@iiug.org >Subject: Re: Naming Conventions: Table and Columns > > >In article <8vgpm5$775$1@news.xmission.com>, 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. > >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:mmenon@apis.dhl.com] >>Sent: 22 November 2000 11:12 >>To: informix-list@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 ;-) Surfer! Send email to: surfer at nevis-view dot demon dot co dot uk "I can resist anything but temptation" - Oscar Wild ;-)