I like to use the Guid values as Primary Keys, because I do a lot of data synchronization and sometimes I need to know the ID value before saving the record into the database. Here is a good article about using the Guid as Primary Key.
ActiveRecord
ActiveRecord is part of the Castle Project and a implementation of the ActiveRecord pattern for .NET. The ActiveRecord pattern consists on instance properties representing a record in the database, instance methods acting on that specific record and static methods acting on all records.
ActiveRecord from Castle is built on top of NHibernate, so you get all its ORM (Object-relational mapping) power but you are free of writing all that ugly XML attribute-based mapping for database-to-object mapping. ActiveRecord will do that for you.
Firebird database
Firebird doesn't provide native Guid support but we can use the CHAR(32) field type instead. Note the performance may suffer some penalties because of using long strings as Primary Keys.
Please note that we use only 32 chars to store a Guid value which normally has 36 chars. This is because the "-" separator is trimmed off. So the f3437ac9-218d-433d-8b48-703a9a982b4c Guid value will be stored as f3437ac9218d433d8b48703a9a982b4c.
ActiveRecord, Firebird and Guid working together
In order to implement my example (a user group hierarchy where each group is able to store multiple child groups and users) I will create first the Firebird database tables:
| TBL_USER | TBL_USER_GROUP |
|
|
ActiveRecord implementation
C# .NET
[ActiveRecord("TBL_USER")]
public class User : ActiveRecordBase
{
[PrimaryKey(PrimaryKeyType.UuidHex,
"`ID`",
UnsavedValue = "00000000000000000000000000000000",
Generator = PrimaryKeyType.UuidHex)]
public string ID
{
get;
set;
}
public Guid Guid
{
get
{
if (String.IsNullOrEmpty(this.ID))
{
return Guid.Empty;
}
else
{
return new Guid(this.ID);
}
}
}
[Property("`USERNAME`")]
public string Username
{
get;
set;
}
[Property("`PWD`")]
public string Password
{
set;
get;
}
[Property("`FIRST_NAME`")]
public string FirstName
{
get;
set;
}
[Property("`LAST_NAME`")]
public string LastName
{
get;
set;
}
[BelongsTo("`USER_GROUP_ID`")]
public UserGroup UserGroup
{
get;
set;
}
}
C# .NET
[ActiveRecord("TBL_USER_GROUP")]
public class UserGroup : ActiveRecordBase
{
[PrimaryKey(PrimaryKeyType.UuidHex,
"`ID`",
UnsavedValue = "00000000000000000000000000000000",
Generator = PrimaryKeyType.UuidHex)]
public string ID
{
get;
set;
}
public Guid Guid
{
get
{
if (String.IsNullOrEmpty(this.ID))
{
return Guid.Empty;
}
else
{
return new Guid(this.ID);
}
}
}
[BelongsTo("`PARENT_USER_GROUP_ID`")]
public UserGroup Parent
{
get;
set;
}
[Property("`NAME`")]
public string Name
{
get;
set;
}
[HasMany(typeof(User), Table = "USER", ColumnKey = "USER_GROUP_ID")]
public IList<User> Users
{
get;
set;
}
}
You have to be careful to set the PrimaryKey attribute like this:
C# .NET
[PrimaryKey(PrimaryKeyType.UuidHex,
"`ID`",
UnsavedValue = "00000000000000000000000000000000",
Generator = PrimaryKeyType.UuidHex)]
The PrimaryKeyType.UuidHex generator will force the ActiveRecord/NHibernate to generate IDs by calling the Guid.NewGuid().ToString() method.
Also, the UnsavedValue = "00000000000000000000000000000000" will inform the ActiveRecord/NHibernate what is the default ID value for the unsaved records. Considering this default value, the ActiveRecord will know if either an INSERT or UPDATE is required when the record is saved into the database.
Finally, this is what we can get with the implementation above:
Posts
5 comments:
While there are advantages to using Guid's as primary keys, such as those illustrated in your post, there is quite a significant drawback that needs to be considered. Since a primary key is stored as a clustered index, your data is kept ordered on disk by this key. The random and un-ordered nature of Guid's means that each insert may carry the additional overhead of re-ordering your data to keep your clustered index ordered. A predictable and sequential value, such as an identity value, means inserts may not incur this re-order overhead. Just some food for though!
You are right about this drawback. Currently I am working on a project which demands the Guid advantages I wrote about. I will see how the INSERT performance is affected by this approach and post the results here. Thanks for your feedback!
Firebird does not use clustered indexes for primary keys. Hence the only drawback is the key size increase.
You may also consider using CHAR(22) for the primary key and populate it with Convert.ToBase64(Guid.ToArray()).
The field size is a problem indeed. But in order to perform data synchronizations, a Guid field is required anyway no matter what the Primary Key field type is. Using a Guid as Primary Key, you don’t need the extra sync check field anymore. Actually, for my scenario here, the Guid Primary Key helped me to save some space despite its size. Anyway, size does matter , so I will switch to CHAR(22) to save even more space and even boost the performance. Thanks for this tip Douglas!
See fluent nhibernate as well. You may not want to litter your nice classes with attributes. I agree that it's way better than XML, though.
As for your GUID problem. It's 32 bytes.. all characters are 0-9a-f. so 2 chars are a byte. You have 16 bytes. Why bother storing them as characters, whether base64 encrypted or not?
Post a Comment