Re: Naming Standards

>From: Nils.Myklebust@idg.no (Nils Myklebust) >Date: Fri, 14 Feb 1997 18:06:01 GMT >X-Informix-List-Id: <news.33930> > >forrey@wsu.edu (Dale Forrey) wrote: >:We are new to the Informix world and the Data Administrator at our >:shop is a little frustrated with the 18 character limit on names. > >I can see this beeing a problem if you want to port a database that >already have longer names. >For new designs it's (mostly) not a problem. Names shouldn't be more >than 18 characters. One place where it might be a small problem is in >naming indexes (which incidentally should always be explicitly named >by you) where the name is always tablename_indexname. With a long >tablename the indexname part may become a tiny problem. > >If you use too long names sql statements become hard to write. > select a_table_with_a_very_long_name.some_very_long_name, > another_table_with_a_very_long_name.some_very_long_name > from a_table_with_a_very_long_name, > another_table_with_a_very_long_name > where a_table_with_a_very_long_name.some_very_long_name = > "Somedata" > and a_table_with_a_very_long_name.some_very_long_name = > another_table_with_a_very_long_name.some_very_long_name I agree about naming indexes, and that using long names can make SQL harder to write. >You can of course rename the tables locally in the from part, but >that's a bad practice (for anything but self joins) that makes the >select harder to read. I disagree. I think it is much easier to read the following than the version above. I think the careful use of case (upper for keywords, lower or mixed for database objects) helps, too, as does careful indenting. You can argue about details of the indenting. SELECT A.some_very_long_name, B.some_very_long_name FROM a_table_with_a_very_long_name A, another_table_with_a_very_long_name B WHERE A.some_very_long_name = "Somedata" AND A.some_very_long_name = B.some_very_long_name Another time when the use of the table abbreviations is very helpful is if you are dealing with remote tables and/or MODE ANSI databases where you have to specify the user name: SELECT A.some_very_long_name, B.some_very_long_name FROM someotherdb@someserver:'user1234'.a_table_with_a_very_long_name A, yetanotherdb@otherserver:'user2345'.another_table_with_a_very_long_name B WHERE A.some_very_long_name = "Somedata" AND A.some_very_long_name = B.some_very_long_name I move towards the other extreme and generally write my SQL with table aliases and identify each column referred to with the relevant table alias unless I'm writing a single-table query. SELECT T.TabName, C.ColNo, C.ColName FROM 'informix'.SysTables T, 'informix'.SysColumns C WHERE T.Tabid = C.Tabid ORDER BY T.TabName, C.ColNo; >As names are case insensitive in SQL you *always* write them in lower >case only of course. Actually, I use the convention that keywords are in upper-case, program variables in I4GL are in lower-case, and database objects are in mixed-case, as in the SysTables example. >:Where did Informix come up with a limit of 18 characters >:in the first place? > >I am quite sure the ANSI standard says 18 characters, in which case it >would be a very bad idea to use longer names. Yes, the ANSI standards (SQL86, SQL-89) only guarantee 18 characters. SQL-92 requires 18 at the entry level (which is all the Informix claims compliance to), and 128 at the Intermediate level. In this detail, Informix conforms to the standard a little too closely for comfort. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>