0

I am working on a project management website which is importing its main data from MS Project 2010 using PSI. The website is used by project managers which are working on it during daytime. The import is done every night, so the import performance is not that important, compared to data retrieval.

The main entities are the following:

  • Project
  • Resource
  • Task

In MS Project the tables are using GUID as primary keys. The main entities are related to many tables, some (like ProjectResources, Work - daily log of number of hours planned/actual per resource per task) are also imported from MS Project.

At the moment I have the freedom to modify some tables in our system as the current database structure is poor (lacks primary keys and indexes).

Having in mind that:

  • using GUID as clustered primary key has performance and storage implications
  • almost all the tables in our system (~ 150 tables) are related to these main entities
  • data imported from MS Project is read only in our system

I am thinking of the following structure for each of the main entities:

  • EntityId (int) : PK, clustered (IDENTITY);
  • EntityUid (uniqueidentifier) : INDEX, non-clustered
  • the other columns

Note: the foreign keys will reference EntityId column, GUID will be used only at import to identify which entries have to be inserted or updated.

Another option would be to set the EntityUid the primary key(non-clustered) and the EntityId a clustered index. Which of the two EntityUid and EntityId makes more sense to be set as primary key?

Do you think this approach has any downsides? Or is there any solution that would be more suitable for my project?

Please have in mind that i have read the following articles, but as the solution is always based on requirements, I could really use your advice:

http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

What are the best practices for using a GUID as a primary key, specifically regarding performance?

Guid Primary /Foreign Key dilemma SQL Server

http://blog.codinghorror.com/primary-keys-ids-versus-guids/

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

http://www.codeproject.com/Articles/388157/GUIDs-as-fast-primary-keys-under-multiple-database

uniqueidentifier PK with int Clustered: Which to use as foreign key?

Community
  • 1
  • 1
Flavia Obreja
  • 1,227
  • 7
  • 13
  • 2
    I don't see a problem with your plan. – Tab Alleman Feb 17 '15 at 15:06
  • @TabAlleman Thanks for taking time to read my question. I have made one edit: Which of the two EntityUid and EntityId makes more sense to be set as primary key? – Flavia Obreja Feb 17 '15 at 15:28
  • I think it doesn't matter, as long as both are unique and able to be referenced by a foreign key. – Tab Alleman Feb 17 '15 at 15:38
  • 1
    I don't think it matters if it's `[id] int identity() primary clusered, [guid] uniqueidentifier unique not null nonclustered` or `[id] int identity() unique not null clustered, [guid] uniqueidentifier primary nonclustered`. The indexes will store the row identifiers of the clustered index regardless of the primary key. Relational design wise, the GUID is your primary key, but that's the only consideration I can think of. I think [this](http://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega) best answers the question. – Bacon Bits Feb 17 '15 at 16:15

0 Answers0