Is it OK to generate IDs of entities outside of CRM?

I need to one-way-synchronize external Data with CRM on a regular basis (i.e. nightly).

This includes creating new Records as well as updating existing ones.

That means, I have to keep track of IDs of CRM-Entities created by my synchronization process.

emphasized textI already managed to create and update records in CRM from lines of database-tables so this is not a problem.

Currently, my mapped tables have the following columns

id: The tables primary key set when inserting a new row
new_myentityid: Primary Attribute of the mapped entity, set after the record was created by the synchronization process
new_name etc.: the values of the records attributes

However, I see a way to drastically simplify the whole process:

Instead of having a PrimaryKey (id) in the database and keeping track of the CRM ID (new_myentityid) in a seperate column, I could as well get rid of the id-columns and make the CRM-ID-Column (new_myentityid) primary key of the table and set it when inserting new records (newid()), so basically substitute id with new_myentityid from a database perspective. I could then bulk-upsert via ExecuteMultipleRequest in combination with UpsertRequest.

This way, I would save a column in each mapped table as well as logic to store the CRM IDs after creating them.

Question

Would this be acceptable or is there anything that should make me avoid this?

Solution

Disclaimer: I'm not aware of a best practice for this so this is just my personal opinion on the matter having developed for Dynamics several times.

I think that using the CRM Entity GUID for your primary key is a good idea. It's less complicated and is handled well in SQL. I assume the column in your database is uniqueidentifier.

My only comment is to not generate the GUIDs yourself. Let CRM generate them for you as it does a better job at keeping everything sequential and indexed.

See this blog entry on MSDN for further detail