Does anyone have production databases where table
Not a problem report but a poll: Jonathan Leffler asked whether anyone actually uses database object names (tables, columns, indexes, procedures, etc.) longer than 64 of the allowed 128 characters. Respondents said they rarely exceed 18-40 characters, often staying under 32 for portability with other vendors. Art Kagel noted that very long names do appear in auto-generated index and foreign-key constraint names from ER design tools, and shared his own naming best practices (plural table names, no table-name prefixes on columns, avoid reserved words and type-encoded names). No formal conclusion beyond the consensus that very long names are rare.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We're trying to gauge whether anyone actually uses database object names longer than half the maximum length of 128, so 64 characters or more long. (Characters means bytes in code sets like ISO 8859-15; it means characters in code sets like UTF-8 and GB18030.) Table in the subject line is a convenient shorthand for any database object name: database, server, table, view, procedure, trigger, column, index, UDT, etc. My guess is that there are very few names that long used in practice, but I live and learn. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d0408398956f15a04be207bdb
35-40 characters is about as long as I've seen in practice. Plus some try to stay under 32 characters since other vendors have that limit and keeping to that improves the prospect of portability. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 20, 2012 at 2:30 PM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > We're trying to gauge whether anyone actually uses database object names > longer than half the maximum length of 128, so 64 characters or more long. > (Characters means bytes in code sets like ISO 8859-15; it means characters > in code sets like UTF-8 and GB18030.) > > Table in the subject line is a convenient shorthand for any database object > name: database, server, table, view, procedure, trigger, column, index, > UDT, etc. > > My guess is that there are very few names that long used in practice, but I > live and learn. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --f46d0408398956f15a04be207bdb > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba9590c16b604be216032
I haven't seen anything that large... And when I do I'll probably consider suicide or homicide :) One reason why we don't see it, can be that most people started working when the limits were much shorter, but in any case I think that for anyone to use such a large name it must be because the name includes metadata that doesn't belong there... Regards. On Fri, Apr 20, 2012 at 7:30 PM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > We're trying to gauge whether anyone actually uses database object names > longer than half the maximum length of 128, so 64 characters or more long. > (Characters means bytes in code sets like ISO 8859-15; it means characters > in code sets like UTF-8 and GB18030.) > > Table in the subject line is a convenient shorthand for any database object > name: database, server, table, view, procedure, trigger, column, index, > UDT, etc. > > My guess is that there are very few names that long used in practice, but I > live and learn. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --f46d0408398956f15a04be207bdb > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --485b397dcc9759284404be22a53b
I stay under 18 chars - old habits die hard. If the database is one I've designed then I'm really only interested in the first 5 characters anyway But I do have customers that precede every table column by the table name and other such absurdities Cheers Paul > We're trying to gauge whether anyone actually uses database object names > longer than half the maximum length of 128, so 64 characters or more long. > (Characters means bytes in code sets like ISO 8859-15; it means characters > in code sets like UTF-8 and GB18030.) > > Table in the subject line is a convenient shorthand for any database > object > name: database, server, table, view, procedure, trigger, column, index, > UDT, etc. > > My guess is that there are very few names that long used in practice, but > I > live and learn. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --f46d0408398956f15a04be207bdb > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
What is a good and generally accepted convention for naming tables?.. examples: lookup tables, fact tables, etc.. Ever since SE 2.10.06, I have been using short 3-letter abbreviations for my table names and use the same 3-letters as a prefix when naming columns so that every column name in SYSCOLUMNS is unique. Column name tbl_col_name is the same as saying tbl.col_name, and informix allows you to use just the column name if it is unique within SYSCOLUMNS.
I couldn't imagine anyone using more than 18 chars for a table name, but like you said "live and learn", anything is possible!.. I could imagine that a dynamically generated table name or someone in Germany, where some German words tend to be very long, or a table with a DATETIME suffix might surpass the 32 char length that your post mentions.
I am personally opposed to naming columns starting with the tablename whether it is 3 letters or 18 letters. Personal opinion. Informix only requires you to qualify a column name if it is not unique within the tables listed in the FROM clause if a single query, they do not have to be unique across the entire database. As you say, using <tablename part>_<column specific name part> is no better or worse than <tablename>.<column name> and the latter will entail less typing since you don't have to use the longer form in every reference to the column. Even when you have to qualify the column name, table aliases can be used to reduce the tablename needed to qualify to a one or two letter alias. Here are my best practices when I design a database: - Name tables after the object that they map - Name tables in plural since they contain multiple instances of the object in general - Name columns after the data they contain. If two columns in two tables contain the same data (say a primary and foreign key or a deliberate denomalization for convenience) then they should have the identical name. - Avoid trivial names (like "id", "key", etc.) unless the purpose is obvious and trivial. (I will use seq or sequence for a sequential sub-key, but even that makes me cringe when I review my own work later.) - Avoid reserved words and other keywords for object names (so no columns named "date", "time", "serial", "from", "alias", "to", etc.). - Avoid naming schemes (like polish notation) that include data types in the column name. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, May 6, 2012 at 1:21 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > What is a good and generally accepted convention for naming tables?.. > examples: lookup tables, fact tables, etc.. Ever since SE 2.10.06, I have > been > using short 3-letter abbreviations for my table names and use the same > 3-letters as a prefix when naming columns so that every column name in > SYSCOLUMNS is unique. Column name tbl_col_name is the same as saying > tbl.col_name, and informix allows you to use just the column name if it is > unique within SYSCOLUMNS. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9341199fe359d04bf63a1a7
Longer table and column names are fairly rare out in the world, however, you do see them more often in index and constraint names. Especially when the designer is trying to indicate the purpose of the object, and especially when such objects are automatically named by ER Design applications. I have seen foreign key constraints formatted as: <dependent table>_to_<independent table>_fk<FK ordinal count> so you can get foreign key names like: order_detail_table_to_order_header_table_fk0001 which is 48 characters long, and it might be supported by an index named: order_detail_table_to_order_header_table_fk0001_index_nonunique Ugly, I know, but I've seen it and one ER Diagramming tool I use object naming templates very much like this unless you change them. I used to know a database architect who liked to name indexes using the table name and keys, so order_detail_index_by_customer_order_num_line_num <sigh> And an ex partner of mine liked to name indexes like that while also insisting on having columns named after the table so it would have been: order_detail_index_by_order_detail_customer_order_detail_order_num_order_detail_ line_num Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, May 6, 2012 at 1:30 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > I couldn't imagine anyone using more than 18 chars for a table name, but > like > you said "live and learn", anything is possible!.. I could imagine that a > dynamically generated table name or someone in Germany, where some German > words tend to be very long, or a table with a DATETIME suffix might surpass > the 32 char length that your post mentions. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340c01672cb204bf63ca50