Use Database Identity Fields for Primary Key?

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Use Database Identity Fields for Primary Key?

Post by BLOMASKY »

Am I the only one who really really wants Aware to use the backend database's ability to create the primary key? Currently, Aware has a table with 1 row (table called BAS_IDGEN with field called MAX_ID), that is used to create ID's for BOs. This means that Aware has to read this record, increment it and use that value for the ID. The problem(s) this poses are:

1). It is very delicate. If the value in this table gets updated improperly, then you can't enter ANY new records.
2). For those who use Stored Procedures to create records, you must lock and access this table. Many times you don't know how many new records you are adding. so it gets even more "interesting" to compute how much to increment the MAX_ID
3). If Aware is not the only program that is working with the database (and for many large organizations, they might have a desktop or web app written in other languages to create records, This requires them to have access to the BAS_IDGEN table with the ability to corrupt it.
3 1/2). Selling this to orgs that understand SQL, telling them that they will NOT use identity fields but have to use this "delicate" process is not a pretty picture
4). With large system, the BAS_IDGEN is a very active table with contention issues
5). Imports from external systems are much easier

Bruce
rprinzing
Posts: 5
Joined: Tue Nov 14, 2017 12:27 am
Location: Kansas City, KS

Re: Use Database Identity Fields for Primary Key?

Post by rprinzing »

Thank you for posting this, Bruce. I am DESPERATE for a solution to this extraordinary DB construct. After years of dealing with very large DB's, I can't imagine the lack of scalability on sharing BAS_IDGEN across many apps. Vlad is always keen on our group to define the problem/issue and let him offer a solution. Your list of concerns certainly enumerates the potential issues. Using an Identity column would be a possible solution. MSSQL and MySQL both support that. But what about Derby?
My current SAAS model is architected to allocate a separate DB for each doctor. The more doctors that get added, the more I shudder at complexities with keeping my MAX_ID straight. Most of my SQL stored procedures increment BAS_IDGEN.

Another possibility that would improve things for me:
Move BAS_IDGEN out of BASDB and BASDBTEST to the declared database in the current BSV.
Example:
My app is called Solo.
Database Allocation: Solo
Test database: SoloTest
Therefore, BAS_IDGEN would be in both Solo and SoloTest. That means writing SQL stored procedures (real power) would make a reference to dbo.BAS_IDGEN and would work whether you run in Test or Prod.
Warm Regards,
Robin
_____________________________________
AwareIM 8.4 (build 2721) MS SQL Server 2014 / MySQL , Windows 10, Theme: Default, Browser: Chrome
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Use Database Identity Fields for Primary Key?

Post by johntalbott »

That's a big 10-4. Identity columns are standard database functionality.

I'd also like the option to use GUID's as ID's for global uniqueness to remove the concern of ID collisions altogether.

The GUID option also removes the need for the existence of an BAS_IDGEN table.
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Use Database Identity Fields for Primary Key?

Post by ACDC »

Vlad is always keen on our group to define the problem/issue and let him offer a solution
The silence is killing me, maybe he is still thinking about it
Arnold
Posts: 24
Joined: Mon Apr 09, 2018 11:58 pm
Location: San Francisco Bay Area - California - USA

Re: Use Database Identity Fields for Primary Key?

Post by Arnold »

I concur with all this - the current IDGEN concept makes me really nervous with my plans for my new SaaS product with many tenants and many subscribers/users to all those tenants - all doing heavy data entry, some all day long and congregating in mass during the month end cutoff for lots of them.

I really look forward to what can happen here to enhance the cunning database strategy of Aware IM and Vlad's genius. John Talbot's suggestion eliminating the need for BAS_IDGEN by making the ID attribute a GUID could be the ticket to simplifying this situation and removing this "foreign" action of accessing the top level database for the incremented ID number for the ID for all the records for all the tables for all the databases in the SQL instance - if I understand this right.

This would also remove the situation that Robin and probably Bruce has to contend with in writing their Stored Procedure because of this reference to [database1 or database2 or databasetest].[dbo].[BAS_IDGEN]. I was hoping to follow their lead in doing a lot of work in stored procedures too where it makes sense - again if I understand this right.

Arnold
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

again, if I understand this right

Post by Jaymer »

Arnold wrote:...the current IDGEN concept makes me really nervous with my plans for my new SaaS product with many tenants and many subscribers/users to all those tenants - all doing heavy data entry, some all day long and congregating in mass during the month end cutoff for lots of them.
Howdy Arnold
The issues Bruce, et. al. have, while valid in their context, have nothing to do with THIS issue that you say you are worried about.

Without putting words in Vlad's mouth, I think he'd say that the strategy that Aware uses is proven and works fine. Or else many applications would fail if there was a recurring problem doing "heavy data entry", etc.

BAS_IDGEN doesn't get 'easily corrupted' on its own, from Aware.
It might get corrupted by a programmer writing some bad code in SQL.
Bruce just went through (several weeks ago) a case where some Qty fields were getting totally wiped out in his inventory file making him restore database tables. Intermittent. Happened more than once. Turned out to be faulty code. Not Aware and nothing to do with ID #s.
Bruce has a really complex design. It may be apples & oranges compared to yours. I've used SPs with much faster than Aware performance and the IDGEN issue is not even an issue for me.

So, don't fear, and since you don't have a corporate overseer or Auditors examining your code to see that you don't use Identity fields, then you're blowing this stuff out of proportion. Sure, be concerned about clean design and potential pitfalls, but not this.

jaymer...
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Arnold
Posts: 24
Joined: Mon Apr 09, 2018 11:58 pm
Location: San Francisco Bay Area - California - USA

Re: Use Database Identity Fields for Primary Key?

Post by Arnold »

Hi Jamyer,
I know you still consider me the new kid on the block and rightly so. I appreciate soothing of my tentativeness with the IDGEN. And I can appreciate that it is SOLID!

My point of chiming in again involves - sorry for repeating myself differently:
1. With IDGen, there is a need to hardcode and change the reference to the databaseName in your stored procedure to handle this (Robin actually showed me code that this was pertinent in a number of places):
[BASDB or BASDBTEST].[dbo].[BAS_IDGEN]. Only impacts you if you use STORED PROCEDURES I guess.

2. I saw daylight of possibly making my second class citizen "external databases" use the primary key GUID it already has - so I can actually utilize the database from an Aware IM web app concurrently from my seasoned desktop application still in use.

3. Just was hoping for the option to not use IDGEN and let the backend use newguid() (which I do now) or whatever.

Not being an expert in either AwareIM or SQL - I will have to defer to much smarter people here but those were my concerns.

Thanks again Jaymer for trying to alleviate my fears.

Arnold
Last edited by Arnold on Mon Jan 28, 2019 10:11 pm, edited 2 times in total.
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Use Database Identity Fields for Primary Key?

Post by ACDC »

If Aware is not the only program that is working with the database (and for many large organizations, they might have a desktop or web app written in other languages to create records, This requires them to have access to the BAS_IDGEN table with the ability to corrupt it.
3 1/2). Selling this to orgs that understand SQL, telling them that they will NOT use identity fields but have to use this "delicate" process is not a pretty picture
One of the solutions is not to touch the BAS_IDGEN table but rather segment a range of ID nos to work with for a particular outside process or remote database synchronising. So create your own counter and start assigning ID nos from 50 000 000 upwards. This way you don't have to worry about locking the table or corrupting BAS_IDGEN
If BAS_IDGEN ever reaches its limit of 49 999 999, then re-segment the ID range on all counters
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Use Database Identity Fields for Primary Key?

Post by Jaymer »

ACDC wrote: One of the solutions is not to touch the BAS_IDGEN table but rather segment a range of ID nos to work with for a particular outside process or remote database synchronising. So create your own counter and start assigning ID nos from 50 000 000 upwards. This way you don't have to worry about locking the table or corrupting BAS_IDGEN
If BAS_IDGEN ever reaches its limit of 49 999 999, then re-segment the ID range on all counters
Right, I get data nightly into a system, and integrate it into production data, but all of the data that comes from that Source I number them up in the 30 million range, where those IDs are never going to intercept with the individually created ones that are much lower. So I never have to go "request " an ID number.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: Use Database Identity Fields for Primary Key?

Post by aware_support »

Well, the current BAS_IDGEN solution certainly works, but we do hear those that write a lot of stored procedures as well.

Problem is that a change in this area will most likely be a massive job, so we have to weigh the benefits against the time and risk of implementing the change.

We haven't made the final decision on this yet...
Aware IM Support Team
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Use Database Identity Fields for Primary Key?

Post by ACDC »

So I never have to go "request " an ID number.
Exactly, all plain sailing from thereon
rprinzing
Posts: 5
Joined: Tue Nov 14, 2017 12:27 am
Location: Kansas City, KS

Re: Use Database Identity Fields for Primary Key?

Post by rprinzing »

Thank support for weighing in and your thoughtful consideration. You have correctly heard about the concern when stored procedures are involved. Perhaps that will drive a solution not yet discovered!
Warm Regards,
Robin
_____________________________________
AwareIM 8.4 (build 2721) MS SQL Server 2014 / MySQL , Windows 10, Theme: Default, Browser: Chrome
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Use Database Identity Fields for Primary Key?

Post by BLOMASKY »

Jaymer wrote:
Right, I get data nightly into a system, and integrate it into production data, but all of the data that comes from that Source I number them up in the 30 million range, where those IDs are never going to intercept with the individually created ones that are much lower. So I never have to go "request " an ID number.
This is fine since you are doing this work. However, so many systems require integration with other systems where a non-aware programmer is created records. Yes, you can tell them that you are using an external table to keep track of the last ID field used, however, I hate being laughed at! This can also create contention when multiple external processes are creating records. This makes it a hard sell when trying to add aware to an existing app, or when pitching aware as the backend processing for a web-based system.

Bruce
ACDC
Posts: 1138
Joined: Sat Jun 30, 2007 5:03 pm
Location: California, USA

Re: Use Database Identity Fields for Primary Key?

Post by ACDC »

There are pros and cons of moving to GUID, all you have to do is google GUID VS INT and see the concerns of using GUID as the ID, specifically regarding database performance.

If AwareIM decides to go with GUID it should be optional, so a developer can choose based on the circumstance and type of application. I use the ID number for numbering and naming of documents and transactions. I can't imagine using a GUID for that purpose. The simplicity of using the ID for its uniqueness and ordered sequence across the DB is compelling.

As to dealing with remote access from other apps, I will stick to the discipline of exposing an API or having a DTO for this purpose. I have had too many bad experiences writing directly to the DB. Also bypassing the rules engine is never a good thing in my mind.

The one thing that I do really look forward to is what Robin suggested
Move BAS_IDGEN out of BASDB and BASDBTEST to the declared database in the current BSV.
Anthony
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Use Database Identity Fields for Primary Key?

Post by BLOMASKY »

ACDC wrote:There are pros and cons of moving to GUID, all you have to do is google GUID VS INT and see the concerns of using GUID as the ID, specifically regarding database performance.

If AwareIM decides to go with GUID it should be optional, so a developer can choose based on the circumstance and type of application. I use the ID number for numbering and naming of documents and transactions. I can't imagine using a GUID for that purpose. The simplicity of using the ID for its uniqueness and ordered sequence across the DB is compelling.

As to dealing with remote access from other apps, I will stick to the discipline of exposing an API or having a DTO for this purpose. I have had too many bad experiences writing directly to the DB. Also bypassing the rules engine is never a good thing in my mind.

The one thing that I do really look forward to is what Robin suggested
Move BAS_IDGEN out of BASDB and BASDBTEST to the declared database in the current BSV.
Anthony
my 0.02 cents worth is PLEASE stay with INTs not GUID! Many times doing maintenance, I will find 1 (or more) records that I have to update and it is MUCH easier to type "UPDATE Table set field = 123 where ID between 100001 and 100003"

Bruce
Post Reply