Auto Increment Help...

[SNusa]

Re: Auto Increment Help...

The rule of thumbs as has been shared with me by mature, seasoned database developers is that the field that you use for establishing uniqueness is the field you use for uniqueness and linking child records, and that's it. If you want some type of identification for who generated the record, when and where, then make a field for that, but don't combine a field for record uniqueness and a field for something else together.

I personally use a table (I name it QID for uniQue ID) that has one record for each table in the database with the record holding the next unique number for the next records in that table. Then I have qid_get(tablename as C), a UDF that acquires the next record ID from the QID table (RW_exclusive), increments it, replaces the acquired QID with the increment, and delivers the acquired QID to the script that called qid_get() which is making the new record. I use exclusively character fields for my ID because I prefix the ID with three letters of the table name for a second layer of uniqueness ( QID for the Projects table is PRJ.0000001, Customer table is CUS.0000001 ). It works very well!

Hi Mike; I think I understand the madness (LOL) of your methodology here. An interesting way to do things actually. I presume you go this "extra step" essentially because you're using DBF's and it allows you to avoid auto increment fields entirely?

This raises two other related question:

First: Since you must open the table in RW_exclusive mode, doesn't this still potentially impose record locking issues (at least with the QID table) within your database? Or, does AA handle simultaneous update attempts via your function as a "cue line operation?" (And thus negate the impact of simultaneous/concurrent read/write attempts in a multi-user environment.) ~Hope that made sense.

Second: Could this methodology be (and is it feasible) to implement IF you planning to migrate the application to a SQL backend?
(I'm thinking the practicality of this applies primarily/exclusively when you're working with DBF tables as opposed to a full SQL engine.)

PS: I do like your naming conventions. Provides for fast and easy readability when looking at the QID table even when opened in default browse for viewing.
(Similar thing to what I was striving for in my example.) I was manually creating a unique (and sequential) MEANINGFUL primary ID field without using AA's built-in auto-increment capabilities for similar reasons.

[DaveM]

Re: Auto Increment Help...

Robert, the same ideas flow over to sql. How it is done may be different, but still the same.
The sql's I have used will never have duplicate key fields. Just can't happen.

My thinking is that migrating dbf to sql will just be the tables. You still have to create your connections/joins and field rules/triggers all over again. May be wrong though.

[SNusa]

Re: Auto Increment Help...

Reason I asked is: There'd be little point in doing this with SQL, right?
(Because you don't have the same issues regarding timing and records (tables) held locked due to ai fields being generated in a SQL environment.)

~I made the assumption that the reason for handling fields with a "QID" table (as you have done) was because an app is using DBF's tables.
(Just trying to learn here, as I presently am also working with DBF's.)

[DaveM]

Re: Auto Increment Help...

Key fields will be auto incremented and ai fields do not have to be Key fields in sql I have used with apps I use for them.

you would be kidding your self to think a datetime field could be auto incremented, right?

I work mostly with dbf fields.

[SNusa]

Re: Auto Increment Help...

Key fields will be auto incremented and ai fields do not have to be Key fields in sql I have used with apps I use for them.

you would be kidding your self to think a datetime field could be auto incremented, right?

I work mostly with dbf fields.

Lines crossed. I knew date fields are not auto incremental....
I guess the confusion lies in the notion that I don't really need an auto-incremental field in the customers table. Just need to guarantee uniqueness.
Also, the linking field (in the customers table) is a different field from my date/time generated field.

Not to confuse the matters, but this brings up an interesting concept on database design.....
Exactly what is the criteria for needing an auto-incrementing field in the first place? Transaction counting/continuity, I can see that. Auditing against unscrupulous deletions, I can see that too.
I suspect they're only really needed in a child table so that you can auto-create additional entries in a browse..... In contrast to this: If you have a top level/parent table, they're of little use except for auditing etc....??

Never tried this with a child linked table, but...
If you have a "user entered" unique field (like the combined date/time/user one I suggested) couldn't this table be used as a child table (like detail section of an invoice) without an ai field?
As long as you have the unique field, and a linking field, I suspect you could in fact create a detail transaction in a browse on an invoice form even though there is no auto-increment field in the child table... And still be able to add records from within the browse, right?

(Wouldn't necessarily want to do it that way, but there's no reason you couldn't, right?) ~ Maybe I'm just exhausted, and not thinking straight lol.

[DaveM]

Re: Auto Increment Help...

With an ai field(which is unique) and in order, you can connect child tables to that field with certainty they will not get confused to another field. Child tables do not have to have a unique field or AI for that matter. Their linking field would be entered to be the same as a parent and there may be 2+ child records with the same value as the parent.

In a set, you can also set it so if a parent record is deleted, the matching child records are also deleted.

A linked field in a child that is connecting to a parent can't be ai.

[SNusa]

Re: Auto Increment Help...

With an ai field(which is unique) and in order, you can connect child tables to that field with certainty they will not get confused to another field. Child tables do not have to have a unique field or AI for that matter. Their linking field would be entered to be the same as a parent and there may be 2+ child records with the same value as the parent.

In a set, you can also set it so if a parent record is deleted, the matching child records are also deleted.

A linked field in a child that is connecting to a parent can't be ai.

Most of this I knew. The big thing I began to wonder about is the actual significance of having an ai field in the first place, given the problems associated with their assignment in a multi-user scenario.
Theory is, if you don't need one, and are able to generate uniqueness (important most of the time, particular in an invoice detail scenario etc.), then why bother. A wile back I was involved in a similar discussion.

It was Mike C's response (post #33 to my question years back on a related topic, see link below) that I got me thinking about all this.....
(Since you don't typically need the ai field in the first place, why use one at all?) http://www.alphasoftware.com/alphafo...l=1#post595913

I've always avoided referential integrity in my designs (along with cascade delete etc.) due to performance issues.
Regardless of how many concurrent users are anticipated, t I like things to run "mean and clean" regardless of where/how the code is being used.

(This is also one major reason I struggled with xbasic instead of playing around with the wizards & taking the easy way out lol. (not so easy when something doesn't work as planned etc.) Hence I only use wizards as a last resort for "ideas" when I'm lost. And then, only as a reference for methods and functions required to accomplish a "goal.")

[SNusa]

Re: Auto Increment Help...

I don't know whether it's right or wrong.....
But I do really like the concept of not having to even worry about "locking out a master table" from other users (a possible consequence of implementing ai fields) by avoiding ai fields wherever possible. The "QID" method is interesting, but it does add "overhead." (and complexity)

While "thinking aloud" here, it just dawned on me that I don't really need a single ai field in my entire application..... Go figure!!!
(The only place I actually had one was the top level customers table. And this table was not even part of the first iteration of this complex little tool I've been building.)

(I do realize that AI fields become essentially an "audit trail" which can visually indicate whether any/no transactions have been removed. But that's also something you could easily implement by storing the record quantity of child browse entries/changes in a field on the parent table.) ~ From a conceptual standpoint, "avoiding AI" (via my suggestion) seems pretty efficient (if not elegant) while also dodging a major multi-user pitfall (reliance on the the ai function.) ~ Without adding complexity! :-)

Interesting "core concepts" at the very least!

The other reason I think I think about things like this is how stored data effects user usage/program capabilities in some of the tools I use.
Prime example: Why would ANY developer store a date that looks like 01/30/2015? (They do this all the time.) Dates should be stored in a (visually) natural sorting sequence whenever possible. (In this example, the date should be stored IMO like 2015.0130.)

On that note, I did uncover an anomaly with in the most current version of AA relative to date presentation.....
If you have the windows "short system date" set to anything but the default (like yyyy-mm-dd instead), date fields get "weird."
When you initially enter a date, it takes it one way, but then when you look at it later, it displays it another way.
Can't remember exactly but I think that entering the date (windows short entry date set to yyyy-mm-dd) it accepted the date as 01/30/2015.
But when it displayed the date (within a browse) it showed that same date as 30-01-2015. It actually made it impossible to even update the field after initial entry!
~I've seen this in other apps, so I knew where to look, and sure enough..... Going back to the default took care of the problem.

You may wonder why anyone would bother (or know for that matter) to change the default short date format, well here's a really good answer for that:
I use a GPS application Garmin Basecamp all fall. It just doesn't sequence data imports (properly name the folders for sorting) unless the short date is changed to yyyy-mm-dd.)
What fixes one program breaks another.....

[DaveM]

Re: Auto Increment Help...

Others have had their say, but with hundreds of users in my applications every day and having no problems like they have had tends to make me stay the way I have it.

When they start a new record, it is immediately saved and the rest is done in change mode. Most of the records will disappear if the right key fields are not completed.
Example is car lot app where Last name, First name have to be completed or there is a delete when the adb is closed.

App I am working on right now follows something similar. The users do see the linking field in places, but no way to change it.

[SNusa]

Re: Auto Increment Help...

Just curious Dave about your "incomplete" records which "disappear." Do you have a routine that periodically deletes these unwanted "ghost records?" OR, are you doing the deletes in real time immediately after the form moves to another record etc.... If you're removing the erroneous/ghost records as they are created/abandoned..... I"m guessing it's either a record level event (with code tied to the QID table) or the forms onSave event (after user hits the right submit button) that runs the code

[DaveM]

Re: Auto Increment Help...

It is done is a script - ondatabaseclose
Certain tables are checked and cleared. If done before then, a user could have started a record and may come back to it.

[SNusa]

Re: Auto Increment Help...

It is done is a script - ondatabaseclose
Certain tables are checked and cleared. If done before then, a user could have started a record and may come back to it.

OK, so you're cleaning up the ghost records "in batch form" when users are logged out of application.
I remember someone recommending that technique when referential integrity/cascade update & delete are turned off..... (as they should be for performance considerations)
~Thanks!