Cross posted on my blog
We've bounced around what should be included and how should it work concerning User logins as well as Site Roles. One of the things you will notice in the CSK 1.0 data design is the amount of repeated data. For instance, there are 2 system roles (Everyone &
Authenticated) that is attached to every single person to ever create a login. Now, the point is, every community has these 2 roles created when a community is created. Which means, for 20 communities, these roles are repeated 20 times. It is my belief that
this is one of the major areas to benefit from normalization. Here is what I have come up with so far for these tables: CSK_Community - Contains the base info for a single community PK = CommunityID is a GUID CSK_User - Contains the base information for a
user/login PK = UserID is an int A login could exist more than once, each with a unique ID CSK_Role - Contains information on roles PK = RoleID is an int Basic system roles are used for all communities CSK_CommunityUser - Many-to-many join between Communities
and Users PK = CommunityID + UserID A single user could be mapped to one or more communities One community is mapped to one or more users CSK_CommunityRole - Many-to-many join between Communities and Roles PK = CommunityID + RoleID A single Role could be mapped
to one or more communities One community is mapped to one or more Roles CSK_UserRole - Many-to-many join between Users and Roles PK = RoleID + UserID A Role is be mapped to one or more User A User is mapped to one or more Roles Note: A flag is being added
to the community table to allow users to register for a site but not be automatically authorized. THis will allow the site admin to set it up so he is email notified and must authorize, or the user goes through a challenge/response via email, or the user is
automatically created (as is currently happening). Here are the initial table creation scripts for the above tables: 1: /****** Object: Table [dbo].[CSK_Community] Script Date: 3/25/2004 11:26:37 PM ******/ 2: CREATE TABLE [dbo].[CSK_Community] ( 3: [CommunityID]
uniqueidentifier ROWGUIDCOL NOT NULL , 4: [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 5: [Domain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 6: [SubDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL , 7: [SmtpServer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 8: [DefaultQuota] [int] NOT NULL , 9: [QuotaUsed] [int] NOT NULL , 10: [SortOrder] [int] NOT NULL , 11: [isDisplayed] [bit] NOT NULL , 12: [isTopicMenuEnabled] [bit]
NOT NULL , 13: [isUserAutoAuthorized] [bit] NOT NULL 14: ) ON [PRIMARY] 15: GO 16: 17: /****** Object: Table [dbo].[CSK_CommunityRole] Script Date: 3/25/2004 11:26:38 PM ******/ 18: CREATE TABLE [dbo].[CSK_CommunityRole] ( 19: [CommunityID] [uniqueidentifier]
NOT NULL , 20: [RoleID] [int] NOT NULL 21: ) ON [PRIMARY] 22: GO 23: 24: /****** Object: Table [dbo].[CSK_CommunityUser] Script Date: 3/25/2004 11:26:38 PM ******/ 25: CREATE TABLE [dbo].[CSK_CommunityUser] ( 26: [CommunityID] [uniqueidentifier] NOT NULL ,
27: [UserID] [int] NOT NULL , 28: [isAuthorized] [bit] NOT NULL , 29: [useDBLogin] [bit] NOT NULL 30: ) ON [PRIMARY] 31: GO 32: 33: /****** Object: Table [dbo].[CSK_Role] Script Date: 3/25/2004 11:26:38 PM ******/ 34: CREATE TABLE [dbo].[CSK_Role] ( 35: [RoleID]
[int] IDENTITY (1, 1) NOT NULL , 36: [Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 37: [Description] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 38: [isSystem] [bit] NOT NULL 39: ) ON [PRIMARY] 40: GO 41: 42: /******
Object: Table [dbo].[CSK_User] Script Date: 3/25/2004 11:26:39 PM ******/ 43: CREATE TABLE [dbo].[CSK_User] ( 44: [UserID] [int] IDENTITY (1, 1) NOT NULL , 45: [FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 46: [MiddleName] [nvarchar]
(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 47: [LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 48: [NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 49: [NTLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL , 50: [SiteLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 51: [SitePassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 52: ) ON [PRIMARY] 53: GO 54: 55: /****** Object: Table [dbo].[CSK_UserRole] Script Date: 3/25/2004
11:26:39 PM ******/ 56: CREATE TABLE [dbo].[CSK_UserRole] ( 57: [UserID] [int] NOT NULL , 58: [RoleID] [int] NOT NULL 59: ) ON [PRIMARY] 60: GO 61: 62: ALTER TABLE [dbo].[CSK_Community] ADD 63: CONSTRAINT [DF_CSK_Community_CommunityID] DEFAULT (newid()) FOR
[CommunityID], 64: CONSTRAINT [DF_CSK_Community_isDisplayed] DEFAULT (1) FOR [isDisplayed], 65: CONSTRAINT [DF_CSK_Community_enableTopicMenu] DEFAULT (0) FOR [isTopicMenuEnabled], 66: CONSTRAINT [DF_CSK_Community_isAdminAllowUser] DEFAULT (1) FOR [isUserAutoAuthorized],
67: CONSTRAINT [PK_CSK_Community] PRIMARY KEY CLUSTERED 68: ( 69: [CommunityID] 70: ) ON [PRIMARY] 71: GO 72: 73: ALTER TABLE [dbo].[CSK_CommunityRole] ADD 74: CONSTRAINT [PK_CSK_CommunityRole] PRIMARY KEY CLUSTERED 75: ( 76: [CommunityID], 77: [RoleID] 78:
) ON [PRIMARY] 79: GO 80: 81: ALTER TABLE [dbo].[CSK_CommunityUser] ADD 82: CONSTRAINT [DF_CSK_CommunityUser_isAuthorized] DEFAULT (0) FOR [isAuthorized], 83: CONSTRAINT [DF_CSK_CommunityUser_useNTLogin] DEFAULT (1) FOR [useDBLogin], 84: CONSTRAINT [PK_CSK_CommunityUser]
PRIMARY KEY CLUSTERED 85: ( 86: [CommunityID], 87: [UserID] 88: ) ON [PRIMARY] 89: GO 90: 91: ALTER TABLE [dbo].[CSK_Role] ADD 92: CONSTRAINT [DF_CSK_Role_isSystem] DEFAULT (0) FOR [isSystem], 93: CONSTRAINT [PK_CSK_Role] PRIMARY KEY CLUSTERED 94: ( 95: [RoleID]
96: ) ON [PRIMARY] 97: GO 98: 99: ALTER TABLE [dbo].[CSK_User] ADD 100: CONSTRAINT [PK_CSK_User] PRIMARY KEY CLUSTERED 101: ( 102: [UserID] 103: ) ON [PRIMARY] 104: GO 105: 106: ALTER TABLE [dbo].[CSK_UserRole] ADD 107: CONSTRAINT [PK_UserRole] PRIMARY KEY
CLUSTERED 108: ( 109: [UserID], 110: [RoleID] 111: ) ON [PRIMARY] 112: GO 113: 114: ALTER TABLE [dbo].[CSK_CommunityRole] ADD 115: CONSTRAINT [FK_CSK_CommunityRole_CSK_Community] FOREIGN KEY 116: ( 117: [CommunityID] 118: ) REFERENCES [dbo].[CSK_Community]
( 119: [CommunityID] 120: ) ON DELETE CASCADE , 121: CONSTRAINT [FK_CSK_CommunityRole_CSK_Role] FOREIGN KEY 122: ( 123: [RoleID] 124: ) REFERENCES [dbo].[CSK_Role] ( 125: [RoleID] 126: ) ON DELETE CASCADE 127: GO 128: 129: ALTER TABLE [dbo].[CSK_CommunityUser]
ADD 130: CONSTRAINT [FK_CSK_CommunityUser_CSK_Community] FOREIGN KEY 131: ( 132: [CommunityID] 133: ) REFERENCES [dbo].[CSK_Community] ( 134: [CommunityID] 135: ) ON DELETE CASCADE , 136: CONSTRAINT [FK_CSK_CommunityUser_CSK_User] FOREIGN KEY 137: ( 138: [UserID]
139: ) REFERENCES [dbo].[CSK_User] ( 140: [UserID] 141: ) ON DELETE CASCADE 142: GO 143: 144: ALTER TABLE [dbo].[CSK_UserRole] ADD 145: CONSTRAINT [FK_CSK_UserRole_CSK_Role] FOREIGN KEY 146: ( 147: [RoleID] 148: ) REFERENCES [dbo].[CSK_Role] ( 149: [RoleID]
150: ) ON DELETE CASCADE , 151: CONSTRAINT [FK_CSK_UserRole_CSK_User] FOREIGN KEY 152: ( 153: [UserID] 154: ) REFERENCES [dbo].[CSK_User] ( 155: [UserID] 156: ) ON DELETE CASCADE 157: GO These are not final scripts. Rather, this is a starting point. I'm sure
as we progress through the 2.0 model that we will find more items to add to some of the above tables. Also, I'm not a huge fan of how SQL EM creates scripts so I will be making new scripts that are a lot easier to keep track of.
Keith Barrows
Ad Astra Per Aspera - "To the stars thru difficulties"
OK. I've slimmed down the scripts and have saved them as single files. Now, since there are dependencies in certain tables, I have created the directory structure like this:
SQL - All SQL objects will be found in here in their directories... --Tables - these are the top level tables ----Child Tables - these depend on the tables one level up ------Child Tables - these depend on the tables one level up
I am posting the raw scripts on my Blog again. (See link above)
Keith Barrows
Ad Astra Per Aspera - "To the stars thru difficulties"
Member
30 Points
241 Posts
ASPInsiders
[2.0] - Database - Community, User and Roles
Mar 26, 2004 01:47 AM|StarPilot|LINK
We've bounced around what should be included and how should it work concerning User logins as well as Site Roles. One of the things you will notice in the CSK 1.0 data design is the amount of repeated data. For instance, there are 2 system roles (Everyone & Authenticated) that is attached to every single person to ever create a login. Now, the point is, every community has these 2 roles created when a community is created. Which means, for 20 communities, these roles are repeated 20 times. It is my belief that this is one of the major areas to benefit from normalization. Here is what I have come up with so far for these tables: CSK_Community - Contains the base info for a single community PK = CommunityID is a GUID CSK_User - Contains the base information for a user/login PK = UserID is an int A login could exist more than once, each with a unique ID CSK_Role - Contains information on roles PK = RoleID is an int Basic system roles are used for all communities CSK_CommunityUser - Many-to-many join between Communities and Users PK = CommunityID + UserID A single user could be mapped to one or more communities One community is mapped to one or more users CSK_CommunityRole - Many-to-many join between Communities and Roles PK = CommunityID + RoleID A single Role could be mapped to one or more communities One community is mapped to one or more Roles CSK_UserRole - Many-to-many join between Users and Roles PK = RoleID + UserID A Role is be mapped to one or more User A User is mapped to one or more Roles Note: A flag is being added to the community table to allow users to register for a site but not be automatically authorized. THis will allow the site admin to set it up so he is email notified and must authorize, or the user goes through a challenge/response via email, or the user is automatically created (as is currently happening). Here are the initial table creation scripts for the above tables: 1: /****** Object: Table [dbo].[CSK_Community] Script Date: 3/25/2004 11:26:37 PM ******/ 2: CREATE TABLE [dbo].[CSK_Community] ( 3: [CommunityID] uniqueidentifier ROWGUIDCOL NOT NULL , 4: [Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 5: [Domain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 6: [SubDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 7: [SmtpServer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 8: [DefaultQuota] [int] NOT NULL , 9: [QuotaUsed] [int] NOT NULL , 10: [SortOrder] [int] NOT NULL , 11: [isDisplayed] [bit] NOT NULL , 12: [isTopicMenuEnabled] [bit] NOT NULL , 13: [isUserAutoAuthorized] [bit] NOT NULL 14: ) ON [PRIMARY] 15: GO 16: 17: /****** Object: Table [dbo].[CSK_CommunityRole] Script Date: 3/25/2004 11:26:38 PM ******/ 18: CREATE TABLE [dbo].[CSK_CommunityRole] ( 19: [CommunityID] [uniqueidentifier] NOT NULL , 20: [RoleID] [int] NOT NULL 21: ) ON [PRIMARY] 22: GO 23: 24: /****** Object: Table [dbo].[CSK_CommunityUser] Script Date: 3/25/2004 11:26:38 PM ******/ 25: CREATE TABLE [dbo].[CSK_CommunityUser] ( 26: [CommunityID] [uniqueidentifier] NOT NULL , 27: [UserID] [int] NOT NULL , 28: [isAuthorized] [bit] NOT NULL , 29: [useDBLogin] [bit] NOT NULL 30: ) ON [PRIMARY] 31: GO 32: 33: /****** Object: Table [dbo].[CSK_Role] Script Date: 3/25/2004 11:26:38 PM ******/ 34: CREATE TABLE [dbo].[CSK_Role] ( 35: [RoleID] [int] IDENTITY (1, 1) NOT NULL , 36: [Name] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 37: [Description] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 38: [isSystem] [bit] NOT NULL 39: ) ON [PRIMARY] 40: GO 41: 42: /****** Object: Table [dbo].[CSK_User] Script Date: 3/25/2004 11:26:39 PM ******/ 43: CREATE TABLE [dbo].[CSK_User] ( 44: [UserID] [int] IDENTITY (1, 1) NOT NULL , 45: [FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 46: [MiddleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 47: [LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , 48: [NTDomain] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 49: [NTLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 50: [SiteLogin] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 51: [SitePassword] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 52: ) ON [PRIMARY] 53: GO 54: 55: /****** Object: Table [dbo].[CSK_UserRole] Script Date: 3/25/2004 11:26:39 PM ******/ 56: CREATE TABLE [dbo].[CSK_UserRole] ( 57: [UserID] [int] NOT NULL , 58: [RoleID] [int] NOT NULL 59: ) ON [PRIMARY] 60: GO 61: 62: ALTER TABLE [dbo].[CSK_Community] ADD 63: CONSTRAINT [DF_CSK_Community_CommunityID] DEFAULT (newid()) FOR [CommunityID], 64: CONSTRAINT [DF_CSK_Community_isDisplayed] DEFAULT (1) FOR [isDisplayed], 65: CONSTRAINT [DF_CSK_Community_enableTopicMenu] DEFAULT (0) FOR [isTopicMenuEnabled], 66: CONSTRAINT [DF_CSK_Community_isAdminAllowUser] DEFAULT (1) FOR [isUserAutoAuthorized], 67: CONSTRAINT [PK_CSK_Community] PRIMARY KEY CLUSTERED 68: ( 69: [CommunityID] 70: ) ON [PRIMARY] 71: GO 72: 73: ALTER TABLE [dbo].[CSK_CommunityRole] ADD 74: CONSTRAINT [PK_CSK_CommunityRole] PRIMARY KEY CLUSTERED 75: ( 76: [CommunityID], 77: [RoleID] 78: ) ON [PRIMARY] 79: GO 80: 81: ALTER TABLE [dbo].[CSK_CommunityUser] ADD 82: CONSTRAINT [DF_CSK_CommunityUser_isAuthorized] DEFAULT (0) FOR [isAuthorized], 83: CONSTRAINT [DF_CSK_CommunityUser_useNTLogin] DEFAULT (1) FOR [useDBLogin], 84: CONSTRAINT [PK_CSK_CommunityUser] PRIMARY KEY CLUSTERED 85: ( 86: [CommunityID], 87: [UserID] 88: ) ON [PRIMARY] 89: GO 90: 91: ALTER TABLE [dbo].[CSK_Role] ADD 92: CONSTRAINT [DF_CSK_Role_isSystem] DEFAULT (0) FOR [isSystem], 93: CONSTRAINT [PK_CSK_Role] PRIMARY KEY CLUSTERED 94: ( 95: [RoleID] 96: ) ON [PRIMARY] 97: GO 98: 99: ALTER TABLE [dbo].[CSK_User] ADD 100: CONSTRAINT [PK_CSK_User] PRIMARY KEY CLUSTERED 101: ( 102: [UserID] 103: ) ON [PRIMARY] 104: GO 105: 106: ALTER TABLE [dbo].[CSK_UserRole] ADD 107: CONSTRAINT [PK_UserRole] PRIMARY KEY CLUSTERED 108: ( 109: [UserID], 110: [RoleID] 111: ) ON [PRIMARY] 112: GO 113: 114: ALTER TABLE [dbo].[CSK_CommunityRole] ADD 115: CONSTRAINT [FK_CSK_CommunityRole_CSK_Community] FOREIGN KEY 116: ( 117: [CommunityID] 118: ) REFERENCES [dbo].[CSK_Community] ( 119: [CommunityID] 120: ) ON DELETE CASCADE , 121: CONSTRAINT [FK_CSK_CommunityRole_CSK_Role] FOREIGN KEY 122: ( 123: [RoleID] 124: ) REFERENCES [dbo].[CSK_Role] ( 125: [RoleID] 126: ) ON DELETE CASCADE 127: GO 128: 129: ALTER TABLE [dbo].[CSK_CommunityUser] ADD 130: CONSTRAINT [FK_CSK_CommunityUser_CSK_Community] FOREIGN KEY 131: ( 132: [CommunityID] 133: ) REFERENCES [dbo].[CSK_Community] ( 134: [CommunityID] 135: ) ON DELETE CASCADE , 136: CONSTRAINT [FK_CSK_CommunityUser_CSK_User] FOREIGN KEY 137: ( 138: [UserID] 139: ) REFERENCES [dbo].[CSK_User] ( 140: [UserID] 141: ) ON DELETE CASCADE 142: GO 143: 144: ALTER TABLE [dbo].[CSK_UserRole] ADD 145: CONSTRAINT [FK_CSK_UserRole_CSK_Role] FOREIGN KEY 146: ( 147: [RoleID] 148: ) REFERENCES [dbo].[CSK_Role] ( 149: [RoleID] 150: ) ON DELETE CASCADE , 151: CONSTRAINT [FK_CSK_UserRole_CSK_User] FOREIGN KEY 152: ( 153: [UserID] 154: ) REFERENCES [dbo].[CSK_User] ( 155: [UserID] 156: ) ON DELETE CASCADE 157: GO These are not final scripts. Rather, this is a starting point. I'm sure as we progress through the 2.0 model that we will find more items to add to some of the above tables. Also, I'm not a huge fan of how SQL EM creates scripts so I will be making new scripts that are a lot easier to keep track of.
Ad Astra Per Aspera - "To the stars thru difficulties"
Member
30 Points
241 Posts
ASPInsiders
Re: [2.0] - Database - Community, User and Roles
Mar 26, 2004 01:24 PM|StarPilot|LINK
I am posting the raw scripts on my Blog again. (See link above)
Ad Astra Per Aspera - "To the stars thru difficulties"