Mixing Guid and Ints in a single database

Question

Is there anything inherently wrong or dangerous with mixing the identity types for the sake of ensuring I do not run out of keys in the near future, or should all types in my d开发者_运维百科atabase adhere to the same identity strategy?

Details

A database I am building will require a very large number of tables, and some of them (specifically junction tables) will require an obese amount of entries due to the fact that certain elements are aggregate roots that are used in many places with many-to-many relationships.

While the functionality aspect is working fine, I am concerned about the limitations of an identity column for certain things. Doing the math, it is not hard to forsee reaching the limits of a 32-bit identity within 2 and a half years of running. My initial thought for this was to use a Guid for those specific tables instead of a 32-bit integer, however I have trepidations about mixing the two together. I've heard some pretty bad things about that practice.

I am using nHibernate/Fluent nHibernate to design my mapping to my database. But for example, I have the following structure...

class Member : IIdentity<int>
{
 public virtual int Id { get; set; }
}

class Page : IIdentity<Guid>
{
 public virtual Guid Id { get; set; }
 // multiple other properties
}

There are no less than 8 objects that have an IList<Page>, and a Page can be re-used between multiple objects (hence, it is a many-to-many relationship). So it is defined in my mapping using HasManyToMany. This same principle extends to several other objects.

The great thing about GUID's are that they unique regardless of what database the record comes from. If you are accessing multiple databases or need to identify objects uniquely apart from the schema then you should go with GUIDS.

Otherwise it is best to go with 32 bit integers. Your application sounds resource intensive and you need to consider that integers perform better than GUIDS.

You can use bigint identity instead of int identity. That might give you the breathing room you need without using as much space in your keys. Something else to consider is the insert rate and whether you're going to get hotspots in your indexes. Guids will be closer to random whereas ints (or bigints) are going to be sequential.