Re: Naming Standards

To keep the discussion short (relatively -- it's still a long posting), I'm going to have to be selective in what I quote, so a good deal of what Nils says not commented on. I've also taken the liberty of correcting some of Nils' spellings. I trust this won't cause offence -- if I had to read and write in Norwegian to converse with Nils, we wouldn't be able to start having this argument:-( >From: Nils.Myklebust@idg.no (Nils Myklebust) >Date: Sat, 15 Feb 1997 20:05:22 GMT >X-Informix-List-Id: <news.33972> > >[...] >Elegance in design and programming is one of the more important issues >in attaining quality in finished products. Agreed, and consistency, which is a recurring theme in the discussion below, is an important part of quality in software. >Also an important issue in this context is that non[e] of this is a >"matter of opinon" or related to "feeling". Every *argument* on these >issues, as in every other area of computing (and all other walks of >life), can be and has to be backed by reason. (Jonathan has never >stated otherwise, so this is for the benefit of others.) Unfortunately, two rational people can think that their arguments are conclusive, and still end up disagreeing. This situation is normally reconciled by agreeing that it is a matter of opinion. >[...] Can one do a PS first: Yes, it's called a pre-script, isn't it? :-) >johnl@informix.com (Jonathan Leffler) wrote: >:>From: Nils.Myklebust@idg.no (Nils Myklebust) >:>forrey@wsu.edu (Dale Forrey) wrote: >:>:The Data Administrator [is] frustrated with the 18 character limit on names. >:> >:>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 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 > >I agree in this case on the use of renamed tables (further on case and >indentation below). The problem of readability however stems from two >issues here: >* The names are too long so it is hard to read whatever you do (that was > the point of the whole select statement). >* The names aren't real names, so it gets even harder to read. And because the names aren't real, I couldn't find more useful mnemonics for the two tables than A and B. I was not advocating that the table mnemonics should always start at A and progress through the alphabet; indeed, the labels don't have to be single letters. >The reason why it's a bad practice to rename tables in the from clause >is that you have to constantly refer to the from clause when you read >other parts of the statement to find what table is actually referred >to. Thus isn't a big deal in small statements, but makes it harder to >read large and complex ones. When you get to 14 tables/views mentioned in a single statement (which I've seen), then yes, it can be difficult to choose suitable mnemonics. But such statements are seldom desirable for other reasons (like, I don't understand and my head hurts). However, a SELECT statement with 30 lines of items in the select list is essentially unreadable unless the items are laid out uniformly (eg one column per line, or in neat columns). >In any kind of programming every part of what you write should be as >easy as possible to follow without refering to other parts. You have to refer to the FROM clause to check whether there are any tables listed which are not referenced anywhere else. That means you cannot understand the SELECT statement without checking the FROM clause, so reading the FROM clause to get the mnemonics for the query is no big hardship. And if the mnemonics are standardized across the entire application, then you might not even need to look at the FROM clause until you find that the query doesn't do what you thought... >Referring to other parts take time and is error prone. ("As easy as >possible" obviously means there are strict limits or you end up with even >more unreadable code.) > >A more realistic example would be: > >select customer.custid, customer.firstname, customer.lastname, > customer.adr1, customer.adr2, customer.zip, customer.state, > invhead.invno, invhead.invdate, invhead.duedate, > invhead.amount, invhead.tax, > invline.invno, invline.lineno, invline.prodcode, > invline.proddescr, invline.quantity, invline.amount, invline.tax > from customer, invhead, invline > where customer.custid = 1000 > and customer.custid = invhead.custid > and invhead.invno = invline.invno >[...example using aliases 'a', 'b', 'c' omitted for brevity...] >This have lost significantly in readability. Of course the following >is better: > >select c.custid, c.firstname, c.lastname, > c.adr1, c.adr2, c.zip, c.state, > ih.invno, ih.invdate, ih.duedate, > ih.amount, ih.tax, > il.invno, il.lineno, il.prodcode, > il.proddescr, il.quantity, il.amount, il.tax > from customer c, invhead ih, invline il > where c.custid = 1000 > and c.custid = ih.custid > and ih.invno = il.invno This is the way I'd do it -- I wrote the query with H used in place of ih and L in place of il as the way I'd do it, before I noticed your second version. Of course, I'd also make a case distinction between the keywords and the rest of the query, and I'd want the items in the SELECT list made clearer, too. Using the mnemonic system is to some extent a compromise; it means that you can unambiguously identify which table every column comes from, without repeating the full table name. If the table names are short (eg customer, invhead, invline), then it doesn't matter very much. But, if the table names are significantly longer (such as customer_details, invoice_header, invoice_line_item), then the mnemonics are beneficial; it takes me less time to read the statement because there is less verbiage to be ignored. >But it's still not as good as the first one. On this point, then, we are going to have to disagree. I think this abbreviated version is more comprehensible than the fully written out version. Doubly so if non-local tables or 'owned' table names are needed. And using mnemonic table aliases