[Repost from Dave Burke’s blog]
To support site-wide features like Sueetie Favorites (which I completed today for BlogEngine.NET posts and comments--more later on that), a centralized data structure was required to tie together the data of the various applications that comprise Sueetie. I'm very happy with the results described here, originally published in the Sueetie Wiki. The new Sueetie Data Core structure proved to be so efficient, with so important a role moving forward, it has assumed a presence far beyond its original confines. The Sueetie Data Core has transmogrified into…Sueetie Unification.
The Unification Explained
The Sueetie Framework and its accompanying data schema sit "on top" of the great apps that comprise the Sueetie Online Community. It never modifies existing data objects, but rather extends them through supplement data structures to serve the purposes of the Sueetie Framework in enhancing the original application or integrating it with other site processes.
The Unification Affect is a collection of tables, views and stored procedures, whose main purpose is to tie the disparate application data together in a single unifying structure. While the relationships to the application's data destination may vary, the centralized Unification structure brings a sense of order to multiple datasources functioning as one.
The primary Tables of Unification are Sueetie_Applications and Sueetie_Content, along with Sueetie_ApplicationTypes and Sueetie_ContentTypes.
Sueetie_Applications
For definition, each application in a Sueetie Community is an, um, Application. This wiki is an application, as is the Site Blog, the Forums area, and the Media Gallery. Four applications. A Group is not an application, but each blog within a group is an application, as is the group's forums, wiki and media gallery. Each application has a groupID, or "0" for no group association.
The data below represents the four main applications at the top level of a Sueetie community. This model easily accommodates new applications. Applications in Sueetie Groups would be listed here as well, with each having its associated GroupID key (associated to the Sueetie_Groups table key.)
Sueetie_ApplicationType
The Sueetie_ApplicationType table helps to define the Application, refine queries and relationships. It is very simple as you can see. You would add to this table if you added another type of application and wanted to integrate it into Sueetie searching, favorite tagging, reporting, and other framework services. An example might be a Classifieds area or E-Commerce application.
Sueetie_Content
Sueetie_Content is the main unifying agent in Sueetie Data.Core. All essential post data will find its way to Sueetie_Content. Blog Posts and Comments (of all blogs), Forum posts, wiki pages and media gallery objects. The actual content is stored in the native application's storage objects, but Sueetie_Content contains KeyIDs to that data, designated by ContentID, along with ContentType and ApplicationID.
The data shown above is from the Site's BlogEngine.NET blog. SourceID keys on Sueetie Blog Post and Comment tables created with a BlogEngine.NET Extension to transport XML File Storage to SQL. (See BlogEngine.NET and Sueetie Data Integration for more information.)
Back to Sueetie_Content, ContentTypeID delimits items queried by SourceID (since there is overlap with integer key indexes in various application tables) along with ApplicationID. UserID is the author of the content. A permalink is created as well as DateTimeCreated.
Sueetie_ContentType
We wanted to enable analytics and reporting at the content type level, so we thought about the type of content that comprises a typical Sueetie Site. While more content types are easily added, below are the currently supported ContentTypes.
The Unification Affect In Action
A good example of the Unifying work of these core data objects is in the collection of user favorites. Because we have a centralized data object referencing all site content, all we need to record an item tagged as a user favorite is the UserID and a ContentID Key. The Sueetie_Content and its relationships with the Core supporting tables and the native application's storage location provides all of the information we will need for any Favorites presentation, analysis and processing.
