Carefull - take note of the "suggested" max 10 character field and index name!

[Scholin]

This past week has been one of those dreaded weeks of hours of trying to figure out why my year old web app was starting to act strange and not saving everything but "most things" as well as some other bazaar things that made no sense. My main table is close to being maxed out on file size at 16000 but it wasn't until I went into the table to eliminate some of the unused fields that I realized any field name greater than 10 was truncated.

I have seen this warning in previous posts but thought "my app seems to be solid". That was a mistake.

Others have warned about this in the past and it is a good time to remind people again - keep your file and index names withing the 10 characters! It is a real mess to clean up. I wish I would not have ignored this in the past.

[Doug Page]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Another good point is to go to Cal Locklin's site and download his naming conventions. EXCELLENT!

http://www.aimsdc.net/Tips.htm

[MikeC]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Just to add a bit....there are many who never have a problem with this truncation to 10 characters and therefore claim it should not be an issue.

I agree with them but only up to a point. It should not be an issue with a properly designed database, properly coded, proper use of indices, etc., etc..
But how many Alpha users can claim such expertise!!? Not me for sure! :)

I have had my share of this truncation in the past and even though my coding and such is "better" than it used to be and I have not had it happen for quite some time, it only takes once so why take the chance when the simple measure of using only 10 character eliminates the potential problem?

Michael, I am glad you posted this as a reminder to users; especially novices. Sorry that you have had to go through it though as I do know how long it can take to get things back to where they once were and even more time to correct all the names......:(


I think this should become a "sticky" as some others have become--now to just get new members to actually READ them such as the one that states to read it prior to posting is another thread all its own!!

[drgarytraub]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Hi all,

I have a question: If this is so important, why don't the web app demos that are shipped with alpha adhere to this 10 character limit?

For example, here is a file name:

AJAX_CascadingDropDowns_DBF.AJAX.A5W

And in the various tables, some field names and/or indexes are longer than 10 characters.

??????

Gary

[Steve Workings]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Truncation of table field names is probably due to corruption of the data dictionary where the long field names are stored.

The only place where I warn people about the 10-character limit is with index names where truncation occurs with some reliability, but no predictability.

For everything except index names, I've always practiced and encouraged use of the longer, descriptive names. Helps you remember what you've done, and the next person who has to deal with the database.

[MikeC]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Gary,

If this is so important, why don't the web app demos that are shipped with alpha adhere to this 10 character limit?

I don't think it is an issue at all....unless you are the one that has the truncation occur!! :) The web app you mention falls into the category of something that is scripted/coded correctly overall from the get-go.

Steve,
Not a contradiction really but apparently something that needs to be said again...

The only place where I warn people about the 10-character limit is with index names where truncation occurs

Although this is perhaps the most prevalent area in which truncation occurs, it is by no means the only. In fact I have Never had my indices truncate. It has always been my field names in my tables that were reduced by Alpha which is a rather big PITA seeing as how any form then based upon that table is no longer usable unless the names are once again re-entered with the original longer names (or a backup restored). It can also affect any script/function that has the field used.

Again, it has been quite some time since I had this happen but had happened in more than one database and more than one time. I still have just a few fields that are longer than 10 in these two dbs but anything since does not! No way am I going to take that chance again when the simple measure of just using 10 characters prevents it.

[Tom Cone Jr]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

As has been noted in many previous message board threads a common cause of fieldname truncation copying the DBF and FPT files to a new location without also copying over the accompanying dictionaries. Alpha will create new dictionaries when the database is opened in the new location. The new dictionaries do not have the long fieldnames or any of the other saved information (layouts, field rules, etc.).

Corruption of digital files on hard disks is inevitable. Add the vagaries of random electonic spikes on LAN cables, power surges, the presence of electro magnets in most offices, furniture that pinches cables, aging hardware, network interface cards, and so on it's a wonder anything actually works. If you use long fieldnames you are counting on both the table and its related dictionaries being "in sync" all the time. I prefer to reduce my risk wherever possible by using shortnames. Then only my table has to be "in sync".

[drgarytraub]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

This is a very interesting discussion.

There are apparently some differing opinions. I myself prefer to use the longer names, essentially for the reason that Steve Workings points out, much more descriptive and therefore easier to remember for later fixing, further deveopment, etc. In this regard, it is also useful for the user in some situations. For example, for error messages within validation for grids, the field name is displayed, and I prefer to have a more descriptive field name shown to the user, since that is always displayed by alpha.

With that said, this discussion certainly does concern me. I certainly do not want to jinx myself, but I can never recall ever having a problem with truncation. Never. And I hope I never do. Is this because I write perfect code. I doubt it. But I do follow a very simple procedure each and every day. At the end of every day, I run database compact, sometimes more than once per day. I make multiple backups of the entire database, not just the tables, but all the data dictionaries, definition files, everything.

Given this discussion, do you think that the problem is alleviated by simply doing the database compact so frequently and therefore rebuilding the data dictionaries on at least a once per day basis?

And also, in the event of a problem, there are multiple backups to return to.

Interested in more thoughts on this topic.

Gary

[MikeC]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Gary,

do you think that the problem is alleviated by simply doing the database compact so frequently

I don't think the absence of a problem is due to your diligence in doing this (definitely doesn't hurt!) as I also do this quite often--sounds like I do this even more than you as if there is a problem I want to know as soon as possible so I can restore, if need be, from any of several backups I make during any given session of coding.

This topic has been discussed several times, but is always good to bring it to the forefront every so often I feel, if for not other reason, to bring it to the attention of those who are unaware of this potential problem....then an informed decision can be made. Basically it comes down to what risks you are willing to take....if I had never had it happen, I would very well still probably be using long names.

[Bill Parker]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Like Steve I use long field names. I prefer if index names are 10 characters or less, but I don't restrict myself to that. My index rebuild routine also restores index names, if one should become truncated. I also use order expressions, e.g. tbl.order("my order expression"), wherever possible to reduce dependence on names.

Bill.

[iamko]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Newbie question. I've been designing DBs for over 25 years, always used descriptive field names; 10 chars is not much especially when you try to use an underscore to separate words and add an f on the end like Cal suggests. Seems like this an issue no matter what version of A5 and backend being used, but thought I should ask anyway.

Is this an issue with both DBF and SQL backends (or DBF only)?

Is this still an issue with v10 Web apps?

Does it seem to have more to do with a corrupted data dictionary or not having "properly designed database, properly coded, proper use of indices, etc."?

[Peter.Greulich]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Is this an issue with both DBF and SQL backends (or DBF only)?

DBF only. Tom Cone's post above is relevant - particularly his 1st paragraph. Another way to truncate field names is to open the dbf in Excel or similar and then save it. It's a dbf convention limit. The indexes may truncate at 10 sometimes; not sure why. Just keep'em to 10 or less.

[iamko]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Thank Peter. I've seen several references to this 10 character limit, but not one has mentioned DBF only. I'm going to be using SQL so I'm happy about this.

[DaveM]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

Sorry for the ones that get affected by trucation. Although I respect many on here that use long field names( i still don't agree ), I do not believe it is THAT necessary. Stay to ten on field names, file names, index names and any names you can.

the side advantage is your code can be shorter and sometimes that REALLY matters too.

Yes, I have had truncated field names. Never want that again.

.

[CALocklin]

Re: Carefull - take note of the "suggested" max 10 character field and index name!

As Peter has already pointed out, the 10 character issue is with .dbf files only.

In response to, Does it seem to have more to do with a corrupted data dictionary or not having "properly designed database, properly coded, proper use of indices, etc."? The answer, as Steve Workings actually pointed out earlier, is YES. The problem is that we can all make mistakes and, when that happens, the question is how much work you are willing to go through to fix it.

For those who prefer to use the long field names, I'd like to add another thought. If the names do get truncated, remember that they will get truncated to 10 characters. Why consider this? Because if you try to make the first 10 characters unique (or at least as much as possible) then you won't have as much trouble figuring out which of these truncated fields is the customer's "title" which was originally "Customer_name_title" - vs. Customer_name_last, Customer_name_first, Customer_name_middle, Customer_name_suffix, Customer_name_spouse, etc. Here's the list after truncation:
Customer_n
Customer_n
Customer_n
Customer_n
Customer_n

If you really want to use long names, you could use something that would truncate like this and repairs would be easy:
Cname_firs
Cname_last
Cname_midd
Cname_titl
Cname_suff
or
Cust_first
Cust_last_
Cust_middl
Cust_title
Cust_suffi

But mostly I posted this because I WANT TO EMPHASIZE THIS POINT:

There is NO NEED restrict table names, layout names, operation names, etc. to 10 characters. The 10 character issue is only with field names and index names.

I'm amazed at the number of people I've run into who have carried this 10 character issue over to all names and thus created more confusion than necessary.

There is a restriction on layout name length but I believe that is still 24 characters. There is probably a limit on the length of operation names also. You will know when you hit this limit because it won't allow you to type any more. (Had to look that up - both "anymore" and "any more" would be correct but would have different meanings. Isn't English fun!?)

I wouldn't call anyone a bad developer because they don't use short names for tables/sets, layouts, and operations but here are some more thoughts on why I try to keep my names short...

I do try to keep my layout names, table/set names, and operation names fairly short but not because of any truncation issues and I certainly don't have a 10 character limit to them. The only three reasons for me to keep these names somewhat short is because (1) they are faster to type, (2) some text boxes in development areas are short and long names often require you to scroll to see what's really in there, and (3) expressions do have a character limit and you could run into a complex expression that can't be created because you ran out of characters.

The #3 issue is much less of a concern in recent versions of A5 because Alpha extended the limit but there is still a limit.

Attached is a minor example of issue #2. There are other, worse places to have long names but, since I always use shorter names, I seldom run into the problem and can't remember where it happens. However, every so often I get a customer's database, like in these examples, and run into the problem. It's not insurmountable, it's just annoying. In this example, the table name is Wholesale_invoice_header but there is no other invoice header (like "retail") so why not just call it "Invoice_head" or even "Invc_head" - any database developer should be able to figure those out and both are certainly faster to type. If there was both retail and wholesale, "Wsale_invc_head" should be obvious enough. Even W_invc_head vs. R_invc_head should be pretty simple to figure out. (Note I used "invc" so it couldn't be confused with an inventory header table.)


For those who say short names aren't descriptive enough, I'd say that even long names don't make complete sense unless you understand some basics about how the business works. So, here's the list of fields in my Work_Orders table for a company that installs real estate sign posts. (As someone noted above, all my field names end with "f" to distinguish them from index names or variable names.)

The important things to know about the business are:
- They are typically installing 4x4 wooden sign posts but they could be other sizes or even metal.
- They might optionally have riders and/or flyer boxes.
- If installing riders, they could be owned by the Agent (free) or Stock riders (rented).
- If they have flyer boxes then the installer might also have to put some "welcome flyers" in the boxes.
- Some posts will be charged an extra fee when they are installed "Out Of Area".
- Some posts are installed by subcontractors rather than the post company's own installers.
- A rather unusual factor is that the original order type (put the sign UP, take it DOWN, RESET it, REPLACE something, ADD a rider, etc.) is often not the order type that will end up on the invoice because maybe the sign isn't there (someone moved/stole it) or it couldn't be installed because the address didn't exist or the owner refused to allow it to be installed, etc., etc., etc.
- And, finally, some orders are downloaded from a website.

(I thought this was a simple business when I started this database. Now I'd say it's one of the most complicated situations I've ever dealt with.)

Between knowing the above and seeing the field types, almost anyone should be able to quickly "unabbreviate" each of these short, 10 char or less, names.

Master_nof -- C -- 6 -- 0
Work_ordf -- C -- 6 -- 0
Wo_datef -- D -- 8 -- 0
Callr_namf -- C -- 30 -- 0
Orderd_byf -- C -- 6 -- 0
Rqsd_datef -- D -- 8 -- 0
Ord_typef -- C -- 8 -- 0
Ord_t_invf -- C -- 8 -- 0
Ooa_costf -- N -- 8 -- 2
Rplc_whatf -- C -- 30 -- 0
Sign_typef -- C -- 30 -- 0
Sign_locf -- C -- 11 -- 0
Post_sizef -- C -- 15 -- 0
Post_armf -- C -- 15 -- 0
Post_clrf -- C -- 15 -- 0
Boxf -- L -- 1 -- 0
Box_typef -- C -- 10 -- 0
Qty_boxesf -- N -- 2 -- 0
Welcm_flrf -- L -- 1 -- 0
Leave_flrf -- L -- 1 -- 0
Ridersf -- L -- 1 -- 0
Stk_rdrsf -- C -- 60 -- 0
No_st_rdrf -- N -- 2 -- 0
Agt_ridrsf -- C -- 30 -- 0
No_agt_rdf -- N -- 2 -- 0
Appt_needf -- L -- 1 -- 0
Appt_datef -- D -- 8 -- 0
Appt_timef -- C -- 8 -- 0
Completedf -- L -- 1 -- 0
Comp_onf -- D -- 8 -- 0
Comp_timef -- C -- 8 -- 0
Comp_byf -- C -- 30 -- 0
Refusalf -- L -- 1 -- 0
Qty_rqstdf -- N -- 2 -- 0
Actl_qtyf -- N -- 2 -- 0
Sub_idf -- C -- 6 -- 0
Subf -- C -- 30 -- 0
Sub_faxf -- C -- 12 -- 0
Installerf -- N -- 2 -- 0
Is_invcdf -- D -- 8 -- 0
Recd_by -- C -- 4 -- 0
Last_userf -- C -- 60 -- 0
Statusf -- C -- 8 -- 0
Print_dtf -- D -- 8 -- 0
Create_dtf -- D -- 8 -- 0
Change_dtf -- D -- 8 -- 0
Offc_codef -- C -- 5 -- 0
No_chargef -- L -- 1 -- 0
St_taxf -- L -- 1 -- 0
Lcl_taxf -- L -- 1 -- 0
From_webf -- L -- 1 -- 0
Web_dttmf -- C -- 20 -- 0
Dwnld_dtf -- D -- 8 -- 0

If there are any you can't figure out, it's probably because you don't understand the business and even a longer name wouldn't help without a much longer description. For example, the last two might be "Web_Date_Time" and "Download_Date" but what's the difference? The Web_Date_Time could be expanded to Date_Time_Order_Placed_On_Web and that would be a valid long field name. The "Download_Date" is exactly that - the date it was downloaded. But even with the longer names, you still probably wouldn't understand why both were needed. Orders are normally downloaded at the end of the day so the post company can prepare the install lists for the next day, set up the routes, and maybe even pack the trucks. More than one user has placed an on-line order late at night - after the post company had finished and closed for the day - then complained when it wasn't installed the next day. The Download Date is there as a double check that the orders were actually downloaded in a reasonable time. If Monday night's orders weren't downloaded until Wednesday, then the customer might have a legitimate complaint. (These dates cannot be edited by the post company; i.e, no cheating on this.)

By the way, this is totally unrelated to databases but for those of you who have seen trucks out installing sign posts and thought they were "rinky dink" businesses (as many real estate agents do), consider this...
Some of the post companies I deal with have 7,000 or more posts installed throughout areas as large as 3 counties. At $35-40 dollars each that's about $250,000 of "inventory" spread all over the country and each "inventory item" might sit there for anywhere from a week to 2 years. And, as noted above, most real estate agents don't consider the posts important so they tend not to tell you when the house is sold and the sign should be taken down. Would you want to own a business like that? Invest $250,000 and maybe we will give your stuff back when we're done with it or maybe we'll just throw it away. Then, when you ask us, we'll claim we told you to pick it up and refuse to pay the "lost post" charge.


Sorry for the long post. That seems to happen when I keep getting interrupted and can't finish it in one sitting.