For important tables integer primary keys are best for performance if we need to join the table in a sql query. But even in those tables we often use a guid column as kind of an extra primary key because it comes in handy. For example for any feature that wants to use content ratings, we have a way to do it by using the guid to identify the content to be rated. It can come from any table so we don't have clashes of integer ids if I want to treat multiple tables as if they had a foreign key. With integer ids it can't be done, only 1 table can have the foriegn key integer ids. But with guids they are unique across tables and even machines, so any number of tables can store related data in the mp_ContentRating table (for example) based on the guid ids without any clashes. As long as the guid fields have indexes on them they are almost as performent as real foreign key relationships and more flexible because it can relate to any table that needs to store ratings (but not with a formal foreign key relationship).

Ratings is just one example. We have a number of sub systems in mojoPortal that can be leveraged by custom features using guids, including content version history, content workflow, meta data, friendly urls and planned features such as content comments and content tagging. All with the idea that we don't want to have to re-implement these things for each feature that needs them. So instead we make it possible for any feature to store and retrieve its related data from these sub systems based on guids.

Once a guid is established for an item it should never change just like any primary key should not. Another potential side benefit to guids on tables that really use integer primary keys, is the idea that data from different installations could be merged into one db and though they would get new integer ids on the way in, the relationships could be re-established based on the guids.

Best,

Joe

ModuleId and ModuleGuid both identify an instance of a feature, like an instance of an Html feature, or google map, or an instance of the blog feature.

The purpose of the moduleGuid is as I said before it comes in handy when you want to have ratings, at the module level. ModuleId is the real primary key but ModuleGuid is also a unique identifier of the instance. For some features this is the only identifier, but for more complex features like the blog we have an ItemId and ItemGuid for each blog post. The Guid is again for the same purposes mentioned. So for a blog the moduleguid represents the whole blog and the itemguid represents a blog post.

The Html feature has an ItemID but it isn't used (maybe that is what is confusing you), only ModuleId and ModuleGuid are used in the Html feature but those extra columns were from a long time ago when I thought I wanted to have multiple items in the html  feature. At some point the extra columns may be removed.

So whether you need item level ids depends on the feature. For the forums feature it goes even deeper because a forum instance can have multiple forums each with an id, and each forum also has threads with a thread id and posts with post id. The forum is the only feature where I have not yet added guid columns as additional keys.

It is partly for historical reasons. When I first began the mojoPortal project in 2004 I first implemented support for MySql using the MyIsam table type which does not support foreign keys, so a the time I made the decision that we would not use cascading deletes or that kind of thing and application logic would be used to delete child rows rather than cascade deletes based on foreign keys. For consistency I've followed the same approach in all the data layers. A few years ago we changed from MyIsam to Innodb tables which do support foreign keys but I've only used them in a few places since then and still we use application logic for managing child rows and data integrity so often we use just normal indexes rather than foreign keys. I also find that in general I'd rather risk a few orphan rows than have cascading deletes that wipe out all related data or errors due to foreign key constraints. If I delete a user it doesn't mean I want to delete orders that user may have made in webstore for example, I'd rather have an orphan order than have it deleted.

Also there are many situations where we join against common tables based on guids, like I can store ratings for Html or blogs or pages or users or products in the mp_ContentRating table because each thing can retrieve its ratings based on an indexed contentguid column in mp_Ratings that may come from/relate to the mp_Html, mp_Blog, and other tables. If I was stuck in the foreign key mentality, then when I first implemented ratings for the Html feature I would have had a foreign key on the html table, but there can only be one foreign key table for a given column so then I would need separate rating tables for each feature.

Best,

Joe