Try the Web 2.0 Hype Generator!
 
Microsoft Screen Cleaner: For When Your Screen Needs a Little Extra care!
 
SunriseXP Tools Home
 
 
 

Powered By SunriseXPThis web page provides detailed information about the database design of the SunriseXP database, as implemented for the Microsoft SQL Server database. Specifically, the page provides the SQL statements used to create each database table, along with a description of the intended use of the table.

1. Core Module

This module supports the core infrastructure capabilities of the SunriseXP system.

SR_LOG_EVENT

This table us used to record events, such as errors, warnings or other conditions.

CREATE TABLE sr_log_event (
   event_idint IDENTITY(1,1),
   app_idint NOT NULL,
   event_msgvarchar(1000) NOT NULL,
   event_datedatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (event_id), 
   FOREIGN KEY (app_id)REFERENCES sr_application
); 

2. Module Management Module

This module defines the information that is tracked for modules and applications. A single Microsoft SQL Server database can be used to maintain content and data for multiple applications.

SR_APPLICATION

This table defines the master list of applications using the database, where each application is typically (although not necessarily) a distinct web site. The "app_key" column defines a generated key that can be used to look up an application ID; it provides support so that multiple web sites can share the same database (although sharing is not generally the preferred situation).

CREATE TABLE sr_application (
   app_idint IDENTITY(1,1),
   app_namevarchar(100) NOT NULL,
   app_descvarchar(500) NOT NULL,
   app_keyvarchar(200) NOT NULL,
   active_indchar(1) NOT NULL DEFAULT 'A'
    CHECK (active_ind IN ('A', 'I')),
   multi_manage_indchar(1) NOT NULL DEFAULT 'Y'
    CHECK (multi_manage_ind IN ('Y', 'N')),
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (app_id) 
); 

SR_APP_MODULE

This table tracks which modules have been "activated" and made available for use by each application.

CREATE TABLE sr_app_module (
   app_idint NOT NULL,
   module_idint NOT NULL,
   date_activateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (app_id, module_id),
   FOREIGN KEY (app_id)REFERENCES sr_application,
   FOREIGN KEY (module_id)REFERENCES sr_module
); 

SR_MODULE

This module defines the master list of available modules.

CREATE TABLE sr_module (
   module_idint IDENTITY(1,1),
   module_namevarchar(100) NOT NULL,
   module_descvarchar(500) NOT NULL,
   versionvarchar(30) NOT NULL,
   active_indchar(1) NOT NULL DEFAULT 'A'
    CHECK (active_ind IN ('A', 'I')),
   date_installeddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (module_id) 
); 

3. Account Manager Module

SR_PERMISSION_LOOKUP

This lookup table defines the list of permissions that are available and can be assigned to individuals, i.e. - user accounts.

CREATE TABLE sr_permission_lookup (
   permission_idint NOT NULL,
   permission_namevarchar(30) NOT NULL,
   permission_descvarchar(100) NOT NULL,
   PRIMARY KEY (permission_id) 
); 

SR_STATE_LOOKUP

This lookup table stores the definitive list of states within the United States of America.

CREATE TABLE sr_state_lookup (
   state_idint NOT NULL,
   state_codechar(2) NOT NULL,
   state_namevarchar(50) NOT NULL,
   PRIMARY KEY (state_id) 
); 

SR_USERS

This table stores generic information about a user, or, more accurately, a user account.

CREATE TABLE sr_users (
   user_idint IDENTITY(1,1),
   app_idint NOT NULL,
   user_namevarchar(50) NOT NULL,
   display_namevarchar(100) NOT NULL,
   default_user_namevarchar(50) NOT NULL,
   passwordvarchar(30) NOT NULL,
   active_indchar(1) NOT NULL DEFAULT 'A'
    CHECK (active_ind IN ('A', 'I')),
   login_countint NOT NULL DEFAULT 0,
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (user_id) 
   FOREIGN KEY (app_id)REFERENCES sr_application
); 

SR_USER_PROFILE

This table stores detailed information about a person, and is associated with a particular user ID. There may be zero or one profile associated with a user account; this is enforced by software.

CREATE TABLE sr_user_profile (
   profile_idint IDENTITY(1,1),
   state_idint NOT NULL,
   user_idint NOT NULL,
   first_namevarchar(50) NOT NULL,
   last_namevarchar(50) NOT NULL,
   spouse_first_namevarchar(50) NULL,
   spouse_last_namevarchar(50) NULL,
   emailvarchar(100) NULL,
   address1varchar(100) NULL,
   address2varchar(100) NULL,
   cityvarchar(100) NULL,
   zipcodevarchar(30) NULL,
   email_contact_indchar(1) NOT NULL DEFAULT 'Y'
    CHECK (email_contact_ind IN ('Y', 'N')),
   phone_homevarchar(30) NULL,
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (profile_id), 
   FOREIGN KEY (state_id)REFERENCES sr_state_lookup,
   FOREIGN KEY (user_id)REFERENCES sr_users
); 

SR_USER_PERMISSIONS

This associative table records the list of permissions, if any, that have been assigned to a user account.

CREATE TABLE sr_user_permissions (
   user_idint NOT NULL,
   permission_idint NOT NULL,
   PRIMARY KEY (user_id, permission_id),
   FOREIGN KEY (permission_id)REFERENCES sr_permission_lookup,
   FOREIGN KEY (user_id)REFERENCES sr_users
); 

4. Content Management Module

SR_ASSIGNED_CATEGORIES

This associative table records the list of categories, if any, that have been assigned to a content item.

CREATE TABLE sr_assigned_categories (
   category_idint NOT NULL,
   content_idint NOT NULL,
   PRIMARY KEY (category_id, content_id),
   FOREIGN KEY (category_id)REFERENCES sr_category,
   FOREIGN KEY (content_id)REFERENCES sr_content
); 

SR_CATEGORY

This lookup table defines categories that can be used to classify content items.

CREATE TABLE sr_category (
   category_idint IDENTITY(1,1),
   app_idint NOT NULL,
   category_namevarchar(100) NOT NULL,
   category_descvarchar(250) NOT NULL,
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (category_id), 
   FOREIGN KEY (app_id)REFERENCES sr_application
); 

SR_CONTENT

This table defines the content items that can be displayed using the content management features of the web site. The table contains summary information about each content item. Additional content may be found in the SR_PAGE table.

There are a variety of dates associated with content items. The date columns are described below:

  • date_created: The date that the content item was first stored in the database.

  • date_published: The date that the content item will be viewable by users; this may be a future date.

  • date_updated: The date that the content item was last updated. Upon initial entry, the "date_created" and "date_updated" will be the same.

  • display_date: The date to be displayed with a content item. For example, a content item may be entered into database and published (made viewable) on October 10, but the content might originally have been distributed six months earlier in hardcopy. In this case, a display date of April 10 can be displayed to reflect when the content was originally made available.

For the Content Management Module, the date shown with a content item is the "date_published" value, unless the "date_displayed" is defined.

CREATE TABLE sr_content (
   content_idint IDENTITY(1,1),
   app_idint NOT NULL,
   content_type_idint NOT NULL,
   content_group_idint NULL,
   titlevarchar(100) NOT NULL,
   title_linkvarchar(200) NULL,
   summaryvarchar(2000) NULL,
   accessible_indchar(1) NOT NULL DEFAULT 'Y'
    CHECK (accessible_ind IN ('Y', 'N')),
   active_indchar(1) NOT NULL DEFAULT 'A'
    CHECK (active_ind IN ('A', 'I')),
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_publisheddatetime NULL,
   display_datedatetime NULL,
   PRIMARY KEY (content_id), 
   FOREIGN KEY (app_id)REFERENCES sr_application,
   FOREIGN KEY (content_type_id)REFERENCES sr_content_type_lookup,
   FOREIGN KEY (content_group_id)REFERENCES sr_content_group_lookup
); 

SR_CONTENT_GROUP

This table defines content groups, which can be though of as "channels" of content. For example, they can be used to support multiple distinct blogs per site. This table may eventually be renamed to make the channel designation more apparent.

CREATE TABLE sr_content_group (
   content_group_idint IDENTITY(1,1),
   app_idint NOT NULL,
   content_group_titlevarchar(100) NOT NULL,
   title_imagevarchar(200) NULL,
   active_indchar(1) NOT NULL DEFAULT 'A'
    CHECK (active_ind IN ('A', 'I')),
   date_createddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   date_updateddatetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (content_group_id), 
   FOREIGN KEY (app_id)REFERENCES sr_application
); 

SR_CONTENT_TYPE_LOOKUP

This lookup table defines the types of content items that are available for display. Note that the content item types also specify a module ID, allowing different modules to handle their own content types (and allowing modules to ignore other types).

CREATE TABLE sr_content_type_lookup (
   content_type_idint NOT NULL,
   content_type_namevarchar(50) NOT NULL,
   content_type_descvarchar(2000) NOT NULL,
   module_idint NOT NULL,
   PRIMARY KEY (content_type_id), 
   FOREIGN KEY (module_id)REFERENCES sr_module
); 

SR_PAGE

This table defines any pages associated with content items. Note that content items do not have to have pages associated with them.

CREATE TABLE sr_page (
   page_idint IDENTITY(1,1),
   content_idint NOT NULL,
   titlevarchar(100) NOT NULL,
   page_numint NOT NULL,
   page_texttext NULL,
   show_page_title_indchar(1) NOT NULL DEFAULT 'Y'
    CHECK (show_page_title_ind IN ('Y', 'N')),
   PRIMARY KEY (page_id), 
   FOREIGN KEY (content_id)REFERENCES sr_content
); 

SR_PAGE_VIEW_ROLLUP

This table is used to record montly information about page views, where the information is typically extracted from web logs. Note that, despite the name of the table, the stats recorded are the number of page views for a specified content item for a specified month.

CREATE TABLE sr_page_view_rollup (
   rollip_idint IDENTITY(1,1),
   content_idint NOT NULL,
   yearint NOT NULL,
   monthint NOT NULL,
   view_countint NOT NULL,
   PRIMARY KEY (rollup_id), 
   FOREIGN KEY (content_id)REFERENCES sr_content
); 

5. Event Manager Module

This module builds upon the Content Manager module to provide events, i.e. - appointments that can be scheduled for a specified date. Note that events are suitable for display in a list or a calendar.

SR_EVENT

This lookup table defines events, i.e. - scheduled activities.

CREATE TABLE sr_event (
   event_idint IDENTITY(1,1),
   content_idint NOT NULL,
   start_timechar(10) NOT NULL,
   end_timechar(10) NOT NULL,
   PRIMARY KEY (event_id), 
   FOREIGN KEY (content_id)REFERENCES sr_content
); 

6. HOA Module

This module builds upon the Account Manager module to provide features useful for a web site that functions as a community hub for a Home Owners Association (HOA).

SR_HOA_USER

This table contains supplemental profile information about the members of a Home Owners Association.

CREATE TABLE sr_hoa_user (
   user_idint NOT NULL,
   neighborhood_idint NULL,
   resident_indchar(1) NOT NULL DEFAULT 'Y'
    CHECK (resident_ind IN ('Y', 'N')),
   good_standing_indchar(1) NOT NULL DEFAULT 'Y'
      CHECK (good_standing_ind IN ('Y', 'N')),
   PRIMARY KEY (user_id), 
   FOREIGN KEY (neighborhood_id)REFERENCES sr_neighborhood_lookup,
   FOREIGN KEY (user_id)REFERENCES sr_users
); 

SR_NEIGHBORHOOD_LOOKUP

This lookup table defines any neighborhoods that may comprise a community.

CREATE TABLE sr_neighborhood_lookup (
   neighborhood_idint NOT NULL,
   neighborhood_namevarchar(100) NOT NULL,
   PRIMARY KEY (neighborhood_id) 
); 
  
 
  Admin Copyright © 2004 - 2008 David Keener
 		 Site Map