Use Database Identity Fields for Primary Key?

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.

Arnold

BLOMASKY wrote
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

Bruce - what kind of maintenance are you talking about and why is it needed - is it the result of the ID_GEN strategy or just "normal" maintenance? Just curious. Thanks

BLOMASKY

I am either "fixing" stuff that I coded wrong in aware, or (most of the time) the customer needs some data changed (like, WHOOPS, I entered new orders and I forgot to update the selling price 1st. Can you please update the following orders....)

So its stuff like "Update OrderLines set tripNumber = 0 where ID between ......"

Bruce

« Previous Page