DataBaseZone.com
Conrad Muller
Juneau, Alaska

Email: conrad at
databasezone
dot com

A List of the Schema Properties for Managing Users and Accounts


Only the major foriegn key constraints are shown in this diagram.
 
Schema diagram

10/20/2010 14:35:45
Tables Count = 12;    Total Column Count = 97;    Views Count = 1
UDF Count = 0;    Constraint Count = 69;    Stored Procedure Count = 0
Tables
Views
Functions (UDFs)
Constraints
Stored Procedures
 
 Schema.TableNameCount of Records  Table Description
1. dbo.LoginAttempt0 Records  This table stores all user LoginAttempts.
2. dbo.Password3 Records  This table stores all of a users passwords.
3. dbo.User4 Records  User information including contact information.
4. dbo.UserContact8 Records  A users contact information.
5. dbo.UserContactType9 Records  The type of user contact information.
6. dbo.UserGender4 Records  Lists ISO gender codes for User table.
7. dbo.UserNamePrefix7 Records  A prefixes for the user names.
8. dbo.UserNameSuffix12 Records  Suffixes for the user names.
9. dbo.UserRole6 Records  List of user roles for authorization.
10. dbo.UserUserNamePrefix3 Records  Links users to Prefixes.
11. dbo.UserUserNameSuffix1 Records  Links users to suffixes.
12. dbo.UserUserRole7 Records  Links the Users to the UserRoles.

1. dbo.LoginAttempt, 7 Columns, 0 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
LoginAttemptID int Primary Key Identity(1,1)   Unique identifier for each Login Attempt.
AccountName nvarchar(50)       Links to the Account Name.
Password nvarchar(50)     Nullable Password text.
IPNumber nvarchar(50)     Nullable Internet Protocol Number for the connected computer.
BrowserType nvarchar(200)     Nullable Browser Type for the connected computer.
Success bit   ((0))   If true, this entry was Successfull.
CreatedDate datetime   (getdate())   Date and time this entry was created.

2. dbo.Password, 8 Columns, 3 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
PasswordID int Primary Key Identity(1,1)   Unique identifier for each password.
UserID int     Nullable Links to the user.
Password nvarchar(50)     Nullable Password text.
PasswordAnswer nvarchar(50)     Nullable Answer to the multi-authentication question.
PasswordQuestion nvarchar(50)     Nullable A question that provides multiple levels of quthentication.
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.

3. dbo.User, 23 Columns, 4 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserID int Primary Key Identity(1,1)   Unique Identifier for each User.
SupervisorUserID int     Nullable Allows a recursive link from a User to his/her Supervisor.
UserGenderID int   ((0))   Unknown, Male, Female, or lawful person.
AccountName nvarchar(50)     Nullable Name of this users login account.
CommonName nvarchar(100)     Nullable Name commonly used in informal situations.
GivenName nvarchar(100)     Nullable Individual Given Name
MiddleName nvarchar(100)     Nullable Middle Name or initial.
FamilyName nvarchar(100)       Family Name
FullName nvarchar(300)     Nullable The full name as used publicly.
UserNote nvarchar(500)     Nullable Note about this user.
ExternalUser bit   ((0))   If true, this user gets external email instead of internal messages.
Photo varbinary     Nullable ID photo of this user.
Address1 nvarchar(50)     Nullable Address1
Address2 nvarchar(50)     Nullable Address2
City nvarchar(50)     Nullable City
Region nvarchar(50)     Nullable Region
Zip nvarchar(10)     Nullable Postal code as text.
Country nvarchar(50)     Nullable Two character Country Code.
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

4. dbo.UserContact, 6 Columns, 8 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserID int Primary Key     Links to a user.
UserContactTypeID int Primary Key     Links to a Contact Type.
UserContactA nvarchar(200)     Nullable User contact information, column A.
UserContactB nvarchar(200)     Nullable User contact information, column B.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.

5. dbo.UserContactType, 8 Columns, 9 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserContactTypeID int Primary Key Identity(1,1)   Link to table of user Contact Types.
UserContactTypeName nvarchar(50)       The name of this Contact Type for drop-downs.
UserContactTypeNote nvarchar(100)     Nullable A description of the purpose of this Contact Type.
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

6. dbo.UserGender, 6 Columns, 4 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserGenderID int Primary Key     ISO sex code
GenderName nvarchar(50)       Name of gender.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

7. dbo.UserNamePrefix, 8 Columns, 7 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserNamePrefixID int Primary Key Identity(1,1)   Unique identifier for the UserNamePrefix
UserNamePrefix nvarchar(50)       Text of the UserNamePrefix
UserNamePrefixNote nvarchar(250)     Nullable A note explaining the UserNamePrefix
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

8. dbo.UserNameSuffix, 8 Columns, 12 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserNameSuffixID int Primary Key Identity(1,1)   Unique identifier for the UserNameSuffix
UserNameSuffix nvarchar(50)       Text of the UserNameSuffix.
UserNameSuffixNote nvarchar(250)     Nullable A note explaining the UserNameSuffix.
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

9. dbo.UserRole, 8 Columns, 6 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserRoleID int Primary Key Identity(1,1)   Unique Identifier for each UserRole
UserRoleName nvarchar(50)       The display name of this type of user.
UserRoleNote nvarchar(250)     Nullable A description of this role.
Active bit   ((1))   If true, this entry is still active.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.
ModifiedByUserID int     Nullable The UserID which was logged on when this entry was last modified.
ModifiedDate datetime   (getdate())   Date and time the entry was last modified.

10. dbo.UserUserNamePrefix, 4 Columns, 3 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserID int Primary Key     Links to users.
UserNamePrefixID int Primary Key     Links to table of Prefixes.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.

11. dbo.UserUserNameSuffix, 4 Columns, 1 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserID int Primary Key     Links to users.
UserNameSuffixID int Primary Key     Links to table of sufixes.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.

12. dbo.UserUserRole, 6 Columns, 7 Records     Back to Top
Column Name Data Type Is Primary Identity or Default Is Nullable Column Description
UserID int Primary Key     Links to a role in the User table.
UserRoleID int Primary Key     Links to a role in the UserRole table.
BeginDate datetime   (getdate())   Date and time the user role access begins.
EndDate datetime   (getdate())   Date and time the user role access ends.
CreatedByUserID int     Nullable The UserID which was logged on during creation of this entry.
CreatedDate datetime   (getdate())   Date and time the entry was created.

Tables
Views
Functions (UDFs)
Constraints
Stored Procedures
 
 Schema.ViewName   View Description
1. dbo.vUserPassword  Consolidate password data for MS ASP.NET.
 
 
Views     Back to Top
1. dbo.vUserPassword     Back to Top
 Object NameObject TypeDescription
1rankVIEW
2UserIDVIEW
3AccountNameVIEW
4FullNameVIEW
5PasswordVIEW
6PasswordAnswerVIEW
7PasswordQuestionVIEW
8ActiveVIEW
9CreatedDateVIEW
 
Tables
Views
Functions (UDFs)
Constraints
Stored Procedures
 
User Defined Functions     Back to Top
 Object NameObject TypeDescription

Constraints and Triggers     Back to Top
 Object NameObject TypeDescription
1DF__LoginAttempt__CreatedDateDEFAULT_CONSTRAINT 
2DF__LoginAttempt__SuccessDEFAULT_CONSTRAINT 
3DF__NamePrefix__ActiveDEFAULT_CONSTRAINT 
4DF__NamePrefix__CreatedDateDEFAULT_CONSTRAINT 
5DF__NamePrefix__ModifiedDateDEFAULT_CONSTRAINT 
6DF__Password__ActiveDEFAULT_CONSTRAINT 
7DF__Password__CreatedDateDEFAULT_CONSTRAINT 
8DF__User__ActiveDEFAULT_CONSTRAINT 
9DF__User__CreatedDateDEFAULT_CONSTRAINT 
10DF__User__ModifiedDateDEFAULT_CONSTRAINT 
11DF__UserContact__CreatedDateDEFAULT_CONSTRAINT 
12DF__UserContactType__ActiveDEFAULT_CONSTRAINT 
13DF__UserContactType__CreatedDateDEFAULT_CONSTRAINT 
14DF__UserContactType__ModifiedDateDEFAULT_CONSTRAINT 
15DF__UserNameSuffix__ActiveDEFAULT_CONSTRAINT 
16DF__UserNameSuffix__CreatedDateDEFAULT_CONSTRAINT 
17DF__UserNameSuffix__ModifieDateDEFAULT_CONSTRAINT 
18DF__UserRole__ActiveDEFAULT_CONSTRAINT 
19DF__UserRole__CreatedDateDEFAULT_CONSTRAINT 
20DF__UserRole__ModifiedDateDEFAULT_CONSTRAINT 
21DF__UserUserNamePrefix__CreatedDateDEFAULT_CONSTRAINT 
22DF__UserUserNameSuffix__CreatedDateDEFAULT_CONSTRAINT 
23DF__UserUserRole__BeginDateDEFAULT_CONSTRAINT 
24DF__UserUserRole__CreatedDateDEFAULT_CONSTRAINT 
25DF__UserUserRole__EndDateDEFAULT_CONSTRAINT 
26DF_User_ExternalUserDEFAULT_CONSTRAINT 
27DF_User_UserGenderIDDEFAULT_CONSTRAINT 
28DF_UserGender_CreatedDateDEFAULT_CONSTRAINT 
29DF_UserGender_ModifiedDateDEFAULT_CONSTRAINT 
30CreatedByUserID_Password_FK1FOREIGN_KEY_CONSTRAINT 
31CreatedByUserID_User_FK1FOREIGN_KEY_CONSTRAINT 
32CreatedByUserID_UserContact_FK1FOREIGN_KEY_CONSTRAINT 
33CreatedByUserID_UserContactType_FK1FOREIGN_KEY_CONSTRAINT 
34CreatedByUserID_UserGender_FK1FOREIGN_KEY_CONSTRAINT 
35CreatedByUserID_UserNamePrefix_FK1FOREIGN_KEY_CONSTRAINT 
36CreatedByUserID_UserNameSuffix_FK1FOREIGN_KEY_CONSTRAINT 
37CreatedByUserID_UserRole_FK1FOREIGN_KEY_CONSTRAINT 
38CreatedByUserID_UserUserNamePrefix_FK1FOREIGN_KEY_CONSTRAINT 
39CreatedByUserID_UserUserNameSuffix_FK1FOREIGN_KEY_CONSTRAINT 
40CreatedByUserID_UserUserRole_FK1FOREIGN_KEY_CONSTRAINT 
41FK_SupervisorUserID_UserIDFOREIGN_KEY_CONSTRAINT 
42ModifiedByUserID_User_FK1FOREIGN_KEY_CONSTRAINT 
43ModifiedByUserID_UserContactType_FK1FOREIGN_KEY_CONSTRAINT 
44ModifiedByUserID_UserGender_FK1FOREIGN_KEY_CONSTRAINT 
45ModifiedByUserID_UserNamePrefix_FK1FOREIGN_KEY_CONSTRAINT 
46ModifiedByUserID_UserNameSuffix_FK1FOREIGN_KEY_CONSTRAINT 
47ModifiedByUserID_UserRole_FK1FOREIGN_KEY_CONSTRAINT 
48User_Password_FK1FOREIGN_KEY_CONSTRAINT 
49User_UserContact_FK1FOREIGN_KEY_CONSTRAINT 
50User_UserUserNamePrefix_FK1FOREIGN_KEY_CONSTRAINT 
51User_UserUserNameSuffix_FK1FOREIGN_KEY_CONSTRAINT 
52User_UserUserRole_FK1FOREIGN_KEY_CONSTRAINT 
53UserContactType_UserContact_FK1FOREIGN_KEY_CONSTRAINT 
54UserGenderID_User_FK1FOREIGN_KEY_CONSTRAINT 
55UserNamePrefix_UserUserNamePrefix_FK1FOREIGN_KEY_CONSTRAINT 
56UserNameSuffix_UserUserNameSuffix_FK1FOREIGN_KEY_CONSTRAINT 
57UserRole_UserUserRole_FK1FOREIGN_KEY_CONSTRAINT 
58LoginAttempt_PKPRIMARY_KEY_CONSTRAINT 
59Password_PKPRIMARY_KEY_CONSTRAINT 
60PK_UserGenderPRIMARY_KEY_CONSTRAINT 
61User_PKPRIMARY_KEY_CONSTRAINT 
62UserContact_PKPRIMARY_KEY_CONSTRAINT 
63UserContactType_PKPRIMARY_KEY_CONSTRAINT 
64UserNamePrefix_PKPRIMARY_KEY_CONSTRAINT 
65UserNameSuffix_PKPRIMARY_KEY_CONSTRAINT 
66UserRole_PKPRIMARY_KEY_CONSTRAINT 
67UserUserNamePrefix_PKPRIMARY_KEY_CONSTRAINT 
68UserUserNameSuffix_PKPRIMARY_KEY_CONSTRAINT 
69UserUserRole_PKPRIMARY_KEY_CONSTRAINT 

Tables
Views
Functions (UDFs)
Constraints
Stored Procedures
 
Stored Procedures     Back to Top
 Object NameObject TypeDescription

All of Conrad Muller's work on this page is free for your use, but no warrenty is stated or implied.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form