November 16, 2010 at 11:37 am
Hi
For a Portal app, I need to be able to import different SQL/ACCESS databases into my "shared" portal database. (The same database must be able to be imported into the joined portal database, with different “calculated” primary keys)
In the past, I made my primary key based on DatabaseID_PrimaryKey.
So if my DatabaseID is 547 and my primary key is 1234567, then my portal db primary key is 547_1234567.
However, I now face the challenge, the source databases, introduce GUID instead of INT as primary key.
My plan is to make an nvarchar(41) primary key column - GUID is 36 and _9999 is 5, in total 41.
I use LINQ2SQL, and it does not handle composite primary keys/identities well, that’s why a don’t use composite primary keys.
The max rows in each table will be aprox. 100 Databases*30.000 rows = 3.000.000 rows.
Is this a bad way to go, performance wise?
November 17, 2010 at 2:52 am
ce_web (11/16/2010)
Is this a bad way to go, performance wise?
I think it's not a good idea, for at least two reasons:
1) Storing more than one attribute in a column violates 1NF. This alone should be enough to change your mind.
2) Typically, a primary key has a clustered index bound to it. A good clustered index is unique, small and ever increasing. GUID + DatabaseId is not small, nor ever increasing.
I would also argue that uniqueidentifier is a 16-byte GUID and converting it to a 36-byte char just to concatenate an integer doesn't help keeping the key as small as possible.
GUIDs are 99% guaranteed to be unique on a single machine: I don't see the need for concatenating the database Id to enforce uniqueness. I would instead add the database Id as a separate, indexed, column.
Just my two (euro) cents.
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply