How you design your database has huge effect on how your system performs. Primary key, foreign key constraints help to keep the tables linked to each other and store the data in unified manner. From long time SageCRM has followed strategy of generating primary keys through stored procedures rather than relying on the inbuilt primary key features of SQL. Now SageCRM 7.2 follows the principal of Primary keys in SQL, however stores them in similar manner to metadata tables.
Need to Know the “Best Practices for Integrating Third Party Apps with Sage ERP X3″
Join this session on Saturday 8th February, 2014 16h10 – 17h15 to walk through some Best Practices for integrating Third Party or your custom apps with Sage ERP X3 and how each of them is best suited for your app. Some technical points and tips and tricks to definitely consider before you start planning your integration.
Also, get a chance to meet the Greytrix Team Exhibiting at Sage South Africa Annual Conference Sage Insights 2014 from Thursday 6th – 9th February 2014.
Here is how the identity was maintained in previous versions of SageCRM
Unique ID’s for Tables:-
We have SQL table called Custom_Tables. If you go through record in this table, you will come to know all primary and secondary entities are stored with unique id Bord_TableId. This becomes driving parameter in all the CRM tables for saving linked entity records.
Unique ID’s for records:-
To store the record increment value, one more table is present in CRM named SQL_identity. In this table Id_TableId field holds the value of table id from above table and Id_NextId field holds value for next record id.
Let’s consider Cases table for an example.
Table Name: Cases
Table Unique ID: 3
Table Current Record ID: 40848
Table Current Next Record ID: 40849
Entries in SQL_Identity table:-
Entries in Cases table:-
Note: By default unique id start value is always 6001 for all tables.
For every entity the unique id is generated using eware_get_identity_id procedure and relaed parameters are updated in above mentioned tables.
What’s up with Sage CRM 7.2 then?
Now eware_get_identity_id stored procedure is not available in CRM database. Your Record Id fields have become primary keys now. “Identity” property of record id columns in SQL table is set to Yes for uniqueness and auto increment is set to 1.
Rest of the things mentioned above are still behaving in similar manner to support upgrades may be.
How the changed structure can affect?
If you have designed custom entities and calling the eware_get_identity_id procedure manually in the routine you may consider recreating this procedure.
Also Read:
1) How to identify and backup correct Sage CRM Database
2) Retrieve deleted records in Sage CRM
3) Quick look on Sage CRM tables
4) Run SQL Queries through Sage CRM Components
5) Control tab display using multiple SQL clauses