Multi-User 100~300 new records per hour...locking error

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

Thats cool.
I wish the best for you on your project.
I'll start migrating my 100~300 records per hour compontent over to POSTGRES in 2 weeks or so.
And see what happens....

[NoeticCC]

Re: Multi-User 100~300 new records per hour...locking error

Alan, Mike, Bob, Andrea:

Where were you guys a year ago???
Man I wish I met you guys then.

Not (yet) into Alpha!!! (Still struggling with PHP for that matter ;) )

I'm also curious if in the SQL world, (not using A5) if they do something similar to Network optimization?

Depends which part you mean - do you mean record locking or file sharing/locking aspects? The data in SQL databases is always on the server, so any network optimization or shared files involved in any application are on the application side, e.g. PHP files or a PowerBuilder app etc.

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

You answered my question... thats what I thought..

[CALocklin]

Re: Multi-User 100~300 new records per hour...locking error

Just FYI, I agree--I have a major app in which the main key and autoincrement field is numeric and I have never had a problem due to it being numeric. Always found it puzzling why some insist that making such a field a character is superior.

I've been, and continue to be, a proponent of character fields for "ID" fields. In the past I carried this concept over to autoincrement fields for two basic reasons: (1) it is never used for any math calculations and (2) I tended to use the auto-increment field as an ID field. Also, I've run into situations where people wanted to add a prefix or suffix to an "ID" (autoincrement) field. While that kills the simple auto-increment capability, it doesn't mean I can't create my own auto-increment to handle it and the process is much easier if I don't also have to contend with the difficulties of carefully converting both parent and child tables plus necessary changes to scripts to handle the number-to-character issues.

Considering the issue of possible SQL conversions in the future, I would agree that numeric autoincrement fields are probably the better choice. HOWEVER, when people use auto-increment fields I believe they should think very careful about considering it an "ID" field also. (I'm not saying "don't do it"; I'm just saying you should consider it carefully. In fact, my previous choice to use auto-increment fields as "ID" fields is NOT my preference anymore.)

Here's why:

I've run into a number of situations where a customer has initially said something like, "All I need is a number for the ID field." A year or two later they decide they need a prefix or suffix for some reason. Adding a prefix or suffix to a number is rather difficult to say the least. Converting the number field to a character field can be done but it can be difficult and, since it's also the linking value, must be carefully coordinated with all child tables. If the original field was a number, it also typically means changing indexes and scripts because of the differences in handling numbers and characters.

I know of one situation where the user wants to use a W or R to identify customers as wholesale or retail. In this case, a separate ID field consisting of W/R plus the first 3 letters of the last name and the number part of the street address works much better than some apparently random number created by an auto-increment field. A complete ID field also works better than using the auto-incremented "ID" number plus a separate W/R field. The user can look the customer up by the W/R plus 3 letters of the last name then ask for the address if multiple customers are found with the same last name. If instead the customer were asked to remember his "customer number" -- the auto-incremented number -- well, that isn't likely to happen.

The above example is from an old app and the "ID" field is used in place of an auto-increment field. (This app was actually designed by someone else and I'd like to think I'd have been smarter back then but I'm not so sure I can honestly say that.) If I were to do this today, I'd use both an auto-increment field AND the ID field. With the current ID, a customer cannot change between Retail and Wholesale without creating a new number and losing the link to previous data - maybe that's good and maybe not. Also, the female customers can't get married and change their names; and none of the customers are allowed to move to a different address.

OK, those last two were a bit facetious but you get the point. If all the records were linked by a simple, auto-incremented customer number then it would be easy to change the "Customer ID" when people move, get married, or change status. And, if you really want to start a new number when, for example, someone changes from a retail to wholesale customer, just create a new customer!

Well, that was a lot more detail than I originally intended but hopefully it provides some thought provoking concepts.

Edit: I went back and reread some of the earlier posts and see that this is very similar to what Andrea and Dave recommended earlier. And with far fewer words!

[DaveM]

Re: Multi-User 100~300 new records per hour...locking error

Being a sound proponent of numeric to link tables together into sets and be autoincremented, I will counter Cal (in a friendly way) with. My users do not have access to the linking field, which is numeric. It is never in a discussion about that field. In other words, it is out of bounds. It can be used for keeping a record to search on though and is shown on ONE form. It is also printed on one Report. I have other fields that need to be autoincremented and they have to be character. The stock number of a vehicle may look like 80176 and the first trade would be 80176A abd subsequent stock numbers would go like 80176B, then c, etc.

If I am using a set it is connected by the (my field name) acct field to whichever table.

My switch came when the number(character at the time) got to a certain level and suddenly went from like 1999 to 20000000. Can't help it, I lost all interest in using character fields for the purpose. That post is in here somewhere with exact figures



.

[mschoi]

Re: Multi-User 100~300 new records per hour...locking error

NUMERIC VS CHARACTER AUTO-INCREMENT
When doing an append or post operation where the unique-no duplicate key is two or more fields, using the genie A5 does something like this:

Master Table : Invoice_id (N) - Invoice_detl_id (N)
Transaction Tabele : Invoice_id (N) - Invoice_det_id (N)

You will notice that the A5 genie converts it to String
Str(Invoice_id,6,0)-str(Invoice_det_id,6,0) =
which shows up as:
"______15_____18" (underline represents blank spaces)
This is because A5 converts it to character, I'm assuming because A5 does its sorting fundamentally in CHARACTER. However, the blank spaces are there and sometimes seem to need treatment with alltrim() which will make it:
alltrim(Str(Invoice_id,6,0))-alltrim(str(Invoice_det_id,6,0)) =
"1518"
But the problem is this could also be
Invoice_Id #1 and Invoice_det_id #518
instead of
Invoice_Id #15 and Invoice_det_id #18

Do you see the problem?
Has anyone had this type of problem with Numeric Auto-increment? If so what is the solution?

[NoeticCC]

Re: Multi-User 100~300 new records per hour...locking error

Being a sound proponent of numeric to link tables together into sets and be autoincremented, I will counter Cal (in a friendly way) with. My users do not have access to the linking field, which is numeric. It is never in a discussion about that field. In other words, it is out of bounds. It can be used for keeping a record to search on though and is shown on ONE form. It is also printed on one Report.

Same here altho my app was originally built by someone else - the good thing with Alpha5 - and to some degree with things like triggers and so forth in SQL databases - is that you can always use field rules etc. or lookups etc. to show a more user friendly version of what that numerical ID stands for on reports, forms and so on. I guess it may be something that folk more familiar with relational SQL databases are more comfortable with numeric auto-inc IDs because that's all we know for the most part.

[CALocklin]

Re: Multi-User 100~300 new records per hour...locking error

Being a sound proponent of numeric to link tables together into sets and be autoincremented, I will counter Cal (in a friendly way) with....

Actually Dave, my post may be hard to follow but I think we are saying the same thing NOW. I used to be a proponent of character fields for auto-incrementing. For a number of reasons I'm not anymore. In addition, I now limit the use of the auto-incremented field - which I believe is the same thing you are saying but in a different way.

Maybe my use of the term "ID" field was confusing. In this case I'm differentiating between an "ID" field and an auto-incremented field. In the past I used one field for both purposes but I'm finding that doing so can cause problems because that can result in one field that has two purposes - (1) linking tables and (2) being something the user can 'relate' to.

I AM still a proponent of character fields for "ID" fields but my point was that I wouldn't make the "ID" field an auto-incremented field. I want to be able to modify the "ID" field if necessary to identify some change. The "ID" field might still be defined as unique but it would not be my linking field and would not be auto-incremented.

That's not to say that every table with an auto-incremented field also needs an "ID" field - far from it. Only that if it does then I will probably create a separate "ID" field so I can change it, add a prefix/suffix, etc. without affecting the original links. Even if the original use/user doesn't see any need to change the ID field, my experience is that there is a fair chance something will happen in the future to make it desirable on at least one of the ID fields in the app.