CATEGORY CODE MANAGEMENT SYSTEM
Overview
The Category – Code Management (CCM) system is the sub-system that maintains all codes utilized by SAGE applications. The system consists of Codes, Categories, Sub-Categories, and Namespaces. A code is a unique identifier utilized by a SAGE application. Categories are groups of codes with common attributes and are used to identify the codes required for a particular automated process. Sub-Categories further identify codes required for a particular automated process. A Namespace is a category with no parent category. They identify applications in the SAGE system.
CCM maintains both a Display Name and a System Name for all codes in the system to allow authorized users to update codes within the applications they utilize. Administrators identified in the Distributed Administration method grant access to allow the updates.
Main topics/sub-topics
Code
A Code is a unique identifier used for any purpose. All SAGE codes are alpha-numeric due to the use of Enumeration to enable the system to locate a particular code within a flexible system. This can include user types for the User Management System, permit types for the Building application, or district types for the MSBU application. Codes are grouped by categories, which are used to identify the necessary codes for an automated procedure. Codes are associated with only one category.
Category
A Category is a Collection of codes sharing a common attribute. It is a logical grouping of related codes within the SAGE system. Categories are used for the purpose of identifying the set of codes needed for a particular automated process. They may only have one category associated with them, but may have any number of sub-categories and codes associated with it.
Sub-Categories
Sub-Categories are the middle-men in the category – code hierarchy. A sub-category may have any number of sub-categories and codes associated with it, but has only one category associated with it. They are used to further identify which code is necessary for completion of a particular automated process. Sub-Categories are used to group categories and codes. A Namespace is the highest level category used to group categories and codes.
Namespace
A Namespace is used to prevent ambiguity within an application; to distinguish one set of element names from another set. For example, the same code name may be used in more than one automated process. The attributes of the code may be different based on the process by which it is used. Placing the codes in different namespaces allows the differentiation between them, thereby allowing the system to identify the desired code.
A category with no parent category is a Namespace in the SAGE system. Each namespace identifies a SAGE application.
Administration
Distributed Administration is the method employed by the SAGE system to control category and code user access. One or more administrators are identified for each application within the SAGE system. Users identified as administrators are authorized to grant permission to update codes within an application process for which they are responsible.
A method of maintaining two name fields for each category and code enables users to update a code name while preventing the system from losing the record’s associations previously established in the system. Each category and code has a Display Name and a System Name for that purpose.
Display Name
The Display Name is the CategoryName in the SAGE system. It is the field the users view while using an application, and is the field a user changes when they update a code name in an application.
System Name
The System Name is the CodeName in the SAGE system. It is the field the system uses to identify and utilize a category or code. The System Name does not change when a user updates a code name in an application.
Interactions
User Management System (UMS)
Sub-system designed to manage users’ access throughout the entire SAGE system.
Class design
Category class: Used to manage operations and store information (category id, category name, description, etc.) for categories.
Code class: Used to manage operations and store information (code id, code name, description, etc.) for codes.
Class Diagram
Database design
Table Scripts
Categories Table
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[NameText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CategoryNameText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RoleID] [int] NOT NULL ,
[DescriptionText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ParentCategoryID] [int] NULL ,
[ParentCodeID] [int] NULL ,
[NamespaceID] [int] NULL ,
[DeletedBit] [bit] NOT NULL CONSTRAINT [DF__Categorie__Delet__24927208] DEFAULT (0),
[LastUpdUserLoginID] [int] NOT NULL CONSTRAINT [DF_Categories_LastUpdUserLoginID] DEFAULT (60),
[LastUpdDate] [datetime] NOT NULL CONSTRAINT [DF_Categories_LastUpdDate] DEFAULT (getdate()),
[EffectiveDate] [datetime] NULL ,
[ExpirationDate] [datetime] NULL ,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [Categories_Categories_FK2] FOREIGN KEY
(
[NamespaceID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Categories_Categories] FOREIGN KEY
(
[ParentCategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Categories_Categories1] FOREIGN KEY
(
[ParentCategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Categories_Categories2] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Categories_Codes] FOREIGN KEY
(
[ParentCodeID]
) REFERENCES [dbo].[Codes] (
[CodeID]
),
CONSTRAINT [FK_Categories_Role] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[Role] (
[RoleID]
),
CONSTRAINT [FK_Categories_UserLogin] FOREIGN KEY
(
[LastUpdUserLoginID]
) REFERENCES [dbo].[UserLogin] (
[PersonOrPartyID]
)
) ON [PRIMARY]
Codes Table
CREATE TABLE [dbo].[Codes] (
[CodeID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[NameText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeNameText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DescriptionText] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeletedBit] [bit] NOT NULL CONSTRAINT [DF__Codes__DeletedBi__276EDEB3] DEFAULT (0),
[LastUpdUserLoginID] [int] NULL ,
[LastUpdDate] [datetime] NOT NULL CONSTRAINT [DF_Codes_LastUpdDate] DEFAULT (getdate()),
[EffectiveDate] [datetime] NULL ,
[ExpirationDate] [datetime] NULL ,
CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED
(
[CodeID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Codes_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
),
CONSTRAINT [FK_Codes_UserLogin] FOREIGN KEY
(
[LastUpdUserLoginID]
) REFERENCES [dbo].[UserLogin] (
[PersonOrPartyID]
)
) ON [PRIMARY]
CategoriesRole Table - note that the CategoriesRole table operates independently of the Categories and the Codes tables. It adds a layer of functionaly that may be required if there are specific user based capabilities associated with the ability to view or use categories.
CREATE TABLE [dbo].[CategoriesRole] (
[CategoriesRoleID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[RoleID] [int] NOT NULL ,
[AllowAdditionsBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_AllowAdditions] DEFAULT (0),
[AllowUpdatesBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_AllowUpdates] DEFAULT (0),
[AllowDeletionsBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_AllowDeletions] DEFAULT (0),
[AllowPromotionBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_AllowPromotion] DEFAULT (0),
[AllowAdministrationBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_AllowAdministration] DEFAULT (0),
[AllowViewingBit] [bit] NOT NULL ,
[DeletedBit] [bit] NOT NULL CONSTRAINT [DF_CategoriesRole_IsDeleted] DEFAULT (0),
[LastUpdDate] [datetime] NOT NULL CONSTRAINT [DF_CategoriesRole_LastUpdDate] DEFAULT (getdate()),
[LastUpdUserLoginID] [int] NOT NULL ,
CONSTRAINT [PK_CategoriesRole] PRIMARY KEY CLUSTERED
(
[CategoriesRoleID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_CategoriesRole_Categories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[Categories] (
[CategoryID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_CategoriesRole_Role] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[Role] (
[RoleID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_CategoriesRole_UserLogin] FOREIGN KEY
(
[LastUpdUserLoginID]
) REFERENCES [dbo].[UserLogin] (
[PersonOrPartyID]
)
) ON [PRIMARY]
Stored Procedures
GetCategory stored procedure
GO
/****** Object: StoredProcedure [dbo].[usp_ccm_GetCategory] Script Date: 02/12/2009 14:34:49 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_ccm_GetCategory]
(
@CategoryID as int
)
AS
Select * from Categories where CategoryID = @CategoryID and DeletedBit = 0
if @@Error <> 0
Begin
RaisError('SQL Error in usp_ccm_GetCategory Select Category by Category ID', 12,1)
return
End
END
GetCodesforCategory Stored Procedure
CREATE PROCEDURE [dbo].[usp_ccm_GetCodesForACategory]
(
@CategoryID as int
)
AS
Select *
FROM Codes
WHERE CategoryID = @CategoryID and DeletedBit = 0
If @@Error <> 0
Begin
RaisError ('SQL Error in usp_ccm_GetCodesForACategory - Get all codes related to a category',12,1)
End
END
Sored Procedure InsertCategories
GO
/****** Object: StoredProcedure [dbo].[spInsertCategories] Script Date: 02/12/2009 14:44:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsertCategories]
@CategoryNameText varchar(50),
@DescriptionText varchar(250) = null,
@ParentCategoryID int = null,
@ParentCodeID int = null,
@DeletedBit bit = 0,
@LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Categories(
CategoryNameText,
DescriptionText,
ParentCategoryID,
ParentCodeID,
DeletedBit,
LastUpdUserLoginID,
LastUpdDate)
VALUES(
@CategoryNameText,
@DescriptionText,
@ParentCategoryID,
@ParentCodeID,
@DeletedBit,
@LastUpdUserLoginID,
GetDate())
SELECT MAX(CategoryID) as CategoryID FROM Categories
END
Stored Procedure InsertCodes
GO
/****** Object: StoredProcedure [dbo].[spInsertCodes] Script Date: 02/12/2009 14:45:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spInsertCodes]
/* If first field is identity - it should be removed
from the passed parameters and the insert code */
@CategoryID int,
@CodeNameText varchar(250),
@DescriptionText varchar(250) = null,
@DeletedBit bit = 0,
@LastUpdUserLoginID int = null
AS
BEGIN
INSERT INTO Codes(
CategoryID,
CodeNameText,
DescriptionText,
DeletedBit,
LastUpdUserLoginID,
LastUpdDate)
VALUES(
@CategoryID,
@CodeNameText,
@DescriptionText,
@DeletedBit,
@LastUpdUserLoginID,
GetDate())
SELECT MAX(CodeID) FROM Codes
END
Stored Procedure UpdateCategories
GO
/****** Object: StoredProcedure [dbo].[spUpdateCategories] Script Date: 02/12/2009 14:46:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateCategories]
/* This assumes the first line is the PK */
@CategoryID int,
@CategoryNameText varchar(50),
@DescriptionText varchar(250) = null,
@ParentCategoryID int = null,
@ParentCodeID int = null,
@DeletedBit bit,
@LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Categories SET
CategoryNameText = @CategoryNameText,
DescriptionText = @DescriptionText,
ParentCategoryID = @ParentCategoryID,
ParentCodeID = @ParentCodeID,
DeletedBit = @DeletedBit,
LastUpdUserLoginID = @LastUpdUserLoginID,
LastUpdDate = GetDate()
WHERE CategoryID = @CategoryID
SELECT * FROM Categories WHERE CategoryID = @CategoryID
END
Stored Procedure UpdateCodes
GO
/****** Object: StoredProcedure [dbo].[spUpdateCodes] Script Date: 02/12/2009 14:47:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateCodes]
/* This assumes the first line is the PK */
@CodeID int,
@CategoryID int,
@CodeNameText varchar(250),
@DescriptionText varchar(250) = null,
@DeletedBit bit,
@LastUpdUserLoginID int = null
AS
BEGIN
UPDATE Codes SET
CategoryID = @CategoryID,
CodeNameText = @CodeNameText,
DescriptionText = @DescriptionText,
DeletedBit = @DeletedBit,
LastUpdUserLoginID = @LastUpdUserLoginID,
LastUpdDate = GetDate()
WHERE CodeID = @CodeID
SELECT * FROM Codes WHERE CodeID = @CodeID
END
Web services
Category Code Management Subsystem has the following webservice.
WSCCM
This webservice is calling the business logic layer in order to perform operations to manage categories and codes. The methods used by this webservice are:
AssignManagerToCategories: Assigns a role as a manager to one or more categories provided.
Inputs:
roleID (string)
categoryID (string)
loggedInUserID (int)
Outputs:
None
CreateCategory: Creates a Category in the database with the values provided.
Inputs:
name (string)
description (string)
roleID (string)
parentCategoryID (string)
parentCodeID (string)
namespaceID (string)
loggedInUserID (int)
effectiveDate (dateTime)
expirationDate (dateTime)
Outputs:
None
CreateCode: Creates a code in the database with the values specified.
Inputs:
name (string)
description (string)
categoryID (string)
loggedInUserID (int)
effectiveDate (string)
Outputs:
None
CreateNamespace: This method creates a namespace in the CCM System.
Inputs:
name (string)
roleID (string)
description (string)
loggedInUserID (int)
effectiveDate (dateTime)
Outputs:
None
DeleteCategory: Deletes an entire category. If category contains existing codes, exception is thrown.
Inputs:
categoryID (string)
loggedInUserID (int)
expirationDate (dateTime)
Outputs:
None
DeleteCode: Deletes a code from the database.
Inputs:
codeID (string)
loggedInUserID (int)
expirationDate (dateTime)
Outputs:
None
DeleteCodes: Deletes all codes of a category from the database.
Inputs:
codeID (string)
loggedInUserID (int)
expirationDate (dateTime)
Outputs:
None
DeleteNamespace: Deletes the namespace from the CCM System.
Inputs:
codeID (string)
loggedInUserID (int)
expirationDate (dateTime)
Outputs:
None
GetAllChildCodes: Gets the codes corresponding to all the subcategories that belong to a particular category.
Inputs:
categoryID (string)
Outputs:
GetAllChildCodesResult (dataset)
GetCategories: Gets all the categories in the database.
Inputs:
None
Outputs:
GetCategoriesResult (dataset)
GetCategoriesByManager: Get all the categories managed by a role.
Inputs:
roleID (string)
Outputs:
GetCategoriesByManagerResult (dataset)
GetCategoriesByNamespace: Gets all the categories which belong to a particular namespace.
Inputs:
namespaceID (string)
Outputs:
GetCategoriesByNamespaceResult (dataset)
GetCategory: Gets a single category by its ID value.
Inputs:
categoryID (string)
Outputs:
None
GetCategoryByName: Gets the category by its namespace and name.
Inputs:
namespaceID (string)
name (string)
Outputs:
GetCategoryByNameResult (dataset)
GetCode: Gets a single code.
Inputs:
codeID (string)
Outputs:
GetCodeResult(dataset)
GetCodes: Gets all codes related to a category.
Inputs:
categoryID (string)
Outputs:
GetCodesResult (dataset)
GetDependantCategories: Gets all the categories dependant upon either a code or a category, as identified by the enumeration CCTYPE.
Inputs:
id (string)
_type (CCTYPE)
includeDeleted (boolean)
Outputs:
GetDependantCategoriesResult (dataset)
GetDependantCategories2: Gets all the level 2 sub categories of a list of main categories.
Inputs:
categoryID (string)
includeDeleted (boolean)
Outputs:
GetDependantCategories2Result (dataset)
GetDependantCategory: Gets the category which is dependant upon another category and its code.
Inputs:
categoryID (string)
codeID (string)
Outputs:
GetDependantCategoryResult (dataset)
GetDependantCategoryAndCode: Gets the category and its code on which another category is dependant upon. This method returns only the id's.
Inputs:
categoryID (string)
Outputs:
GetDependantCategoryAndCodeResult (dataset)
GetDescription: Gets the description of a category or a code as identified by the CCTYPE Enumeration.
Inputs:
id (string)
type (CCTYPE)
Outputs:
GetDescriptionResult (string)
GetImmediateParent: Gets the immediate parent category to which this given code belongs.
Inputs:
codeID (string)
Outputs:
GetImmediateParentResult (string)
GetNamespaces: Gets all the namespaces present in CCM system.
Inputs:
None
Outputs:
GetNamespacesResult (dataset)
GetXMLTreeByCategory: Returns the CCM Tree of the specified category.
Inputs:
categoryID (string)
Outputs:
GetXMLTreeByCategoryResult (string)
GetXMLTreeByManager: Returns the CCM Tree managed by the role.
Inputs:
roleID (string)
Outputs:
GetXMLTreeByManagerResult (string)
UpdateCategory: Updates the category to contain the values passed in.
Inputs:
categoryID (string)
name (string)
description (string)
parentCategoryID (string)
parentCodeID (string)
namespaceID (string)
loggedInUserID (int)
Outputs:
None
UpdateCode: Updates a code in the database with the values specified.
Inputs:
codeID (string)
name (string)
description (string)
loggedInUserID (int)
Outputs:
None
UpdateManager: Updates the manager role of a category.
Inputs:
roleID (string)
categoryID (string)
loggedinuserid (int)
Outputs:
None
UpdateNamespace: Updates the namespace with the values provided
Inputs:
categoryID (string)
name (string)
roleID (string)
description (string)
loggedInUserID (int)
Outputs:
None
Video Information
A series of videos describing how categories and codes work together is available at
http://ucf-cet4583.pbwiki.com/Category+Code+Manager+ASPNET+Case+Study
Appendix/Glossary
Distributed Administration
Distributed Administration is an administration method that decentralizes system administration thus providing a balanced workload across the organization. This method delegates the responsibilities for changing certain roles and permissions to lower levels of the organizational hierarchy and increases the reliability and through-put of the system. Delegating authority to administrators who are closer to the request source decreases the delay between the time a request is made and the implementation of that request. And it places the responsibility in the hands of individuals who work closely with users who require access to specific portions of the system. Although it is a complicated solution to implement it is easy to manage and is a preferred method for an enterprise-wide Access Management System that maintains more than 1000 internal users and thousands of external users. The Distributed Administration method is utilized by the SAGE system by identifying Super Administrators and Group Administrators within the system. (See the User Management System documentation for additional information.)
Collections
Collections are classes capable of storing other objects. In SAGE collections are implemented using a single class GenericCollection, which is capable of holding objects of any type, for all applications.
Enumeration
Enumeration is a listing of all members from a set of data in some definite sequence. It is a method used in a flexible system to locate data. An executable enumeration is used to create the CodeEnumerations.dll file. The system then uses the file’s auto-generated numeric identifier for each code. Caution must be used due to the potential for new values to be associated with any given code should the executable run again when the system is placed in a new environment such as testing, or production.
Use of an enumeration prevents the ability to assign a numeric name; therefore all codes in the SAGE system must have an alpha-numeric name. However, users do have the ability to assign a numeric code name to the Display Name via the distributed administration method.
Comments (0)
You don't have permission to comment on this page.