Use Database Identity Fields for Primary Key?

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

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.

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.

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

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

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...

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:

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.
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.
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

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

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.

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...

ACDC

So I never have to go "request " an ID number.

Exactly, all plain sailing from thereon

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!

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

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

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

johntalbott

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.

I completely agree that performance is a a big deal. As long as an ID using a GUID is not configured as a clustered index, the hit should be negligible relative the performance constraints of web applications in general.

Specifically within the AwareIM stack of moving parts, the performance bottleneck is never going to be using GUID as an ID.

Business Rules Engine rule processing
Queries - FIND is always translated to a "Select *"
The extra database call to increment and select the new id from the BAS_IDGEN table for every insert
The client-side parsing and rendering of every part of the UI
The generation of the XML containing the data sent over the wire which is then parsed on the client.

If we're looking for top-end performance, AwareIM isn't the right platform.

ACDC wrote
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.

In any event, you can have an Identity column for sequential numbering that is the clustered index if you like.

intra

On a totally non technical topic.

I know you still consider me the new kid on the block and rightly so.

Arnold,

Even if you have [x] amount of years exp with AwareIM, being the 'new kid on the block' shouldn't be a indication of the quality of your response.

Just ask the question. 🙂

I still make silly mistakes all the time! :oops:

customaware

The more you learn, the more you can forget.... 😉

Arnold

Just wondering in this discussion - what strategy would fit best to keep Aware IM working effectively but also be attractive to many new developers to the Aware IM community that have existing databases in production that they want to use Aware IM to develop their the web (and mobile) application version of their seasoned back office desktop application.

It appears that the "external database" concept as it is now is not really supportive of this market of developers. If the "external database" concept were eliminated with a change to the IDGEN concept - that could also eliminate the need to worry about redoing shortcuts every time an external database was updated via Discover Attributes - if I understand this right.

Jaymer

Arnold wrote
It appears that the "external database" concept as it is now is not really supportive of this market of developers.

It would be nice if some development time could be put into "external database" connections period.
...to make it easier, more stable, more features, less restrictive, etc.

Don't know anything about "redoing shortcuts every time", but easier connectivity to bring legacy databases to the Web is a win win for everyone.