Re: Naming Standards

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 allready 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 insidentaly should allways be explisitly named by you) where the name is allways 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 You can of course rename the tables localy in the from part, but that's a bad practice (for anything but self joins) that makes the select harder to read. : Has :anyone come up with a clever set of naming standards that they are willing :to share with the list? For instance, do element names always or ever :reference the entity to which they belong? On indexes yes. (tablename_indexname - allways so you do not get indexes with the same name). The following works very good for databases with up to several hundred tables and a few thousand columns. (Above that other systems *may* become necessary.) On column names some want's a prefix that is the tablename or part of it. That is plain bad design. Columns should have names saying what they contain. If the "same" column (custno perhaps) occurs in many tables it should allways have the same name. If you need more than one such column in the same table you name them with a postfix saying what they are (i.e. custno_payee, custno_deliv...). There are several reasons for this naming convention. One is that you one day need to know every instance of a column in your database. Than you can easily select from syscol where colname matches "colname*". Another reason is you need to easily see equal columns in several tables. That is slightly easier without a prefix. Yet another reason is that several products use equal column names to automatically suggest join path which is a good idea. A prefix is also not needed as the table name itself is the prefix used when you need to spesify the column such as in a select from several tables. Don't fall in the trap of using hungarian notation on your column names (with a prefix giving their database datatype - perhaps c for char, i for integer, d for decimal). It will confuse more than help. First the name itself should be good enough to make the datatype clear (custno is an integer, firstname is a char(30)...). Second the datatype is documented in your database, or better yet in a documentation tool, and should allways be easily accesible by any user. It *may* be a good idea to name columns like this: namefirst, namelast, adrstreet, adrpobox and so on (or possibly name_first, name_last...) where the first part give some idea of the main content (the domain perhaps). Yes this may be seen as a variant on hungarian notation if you like, (or what hungarian notation should have been all along) and in this form it's ok. As names are case insensitive in SQL you *allways* write them in lower case only of course. : Is there a standard set of :abbreviations that you use? Are some words always abbreviated and others :never abbreviated? Try not to use too much abbreviations, but do use the standard once (no for number and so on). Most dictionaries have lists of abbreviations that may be usefull. : 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. : Anyway, thanks for your help. : :Dale Forrey, DBA email: forrey@wsu.edu :Information Technology FAX: (509) 335-0540 :Washington State University Phone: (509) 335-7098 :Pullman, WA 99164-1222 Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org