A List of the Schema Properties for Managing Users and Accounts
Only the major foriegn key constraints are shown in this 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.TableName | Count of Records | Table Description | |
| 1. | dbo.LoginAttempt | 0 Records | This table stores all user LoginAttempts. |
| 2. | dbo.Password | 3 Records | This table stores all of a users passwords. |
| 3. | dbo.User | 4 Records | User information including contact information. |
| 4. | dbo.UserContact | 8 Records | A users contact information. |
| 5. | dbo.UserContactType | 9 Records | The type of user contact information. |
| 6. | dbo.UserGender | 4 Records | Lists ISO gender codes for User table. |
| 7. | dbo.UserNamePrefix | 7 Records | A prefixes for the user names. |
| 8. | dbo.UserNameSuffix | 12 Records | Suffixes for the user names. |
| 9. | dbo.UserRole | 6 Records | List of user roles for authorization. |
| 10. | dbo.UserUserNamePrefix | 3 Records | Links users to Prefixes. |
| 11. | dbo.UserUserNameSuffix | 1 Records | Links users to suffixes. |
| 12. | dbo.UserUserRole | 7 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 Name | Object Type | Description | |
| 1 | rank | VIEW | |
| 2 | UserID | VIEW | |
| 3 | AccountName | VIEW | |
| 4 | FullName | VIEW | |
| 5 | Password | VIEW | |
| 6 | PasswordAnswer | VIEW | |
| 7 | PasswordQuestion | VIEW | |
| 8 | Active | VIEW | |
| 9 | CreatedDate | VIEW | |
| Tables |
| Views |
| Functions (UDFs) |
| Constraints |
| Stored Procedures |
| User Defined Functions Back to Top | |||
| Object Name | Object Type | Description | |
| Constraints and Triggers Back to Top | |||
| Object Name | Object Type | Description | |
| 1 | DF__LoginAttempt__CreatedDate | DEFAULT_CONSTRAINT | |
| 2 | DF__LoginAttempt__Success | DEFAULT_CONSTRAINT | |
| 3 | DF__NamePrefix__Active | DEFAULT_CONSTRAINT | |
| 4 | DF__NamePrefix__CreatedDate | DEFAULT_CONSTRAINT | |
| 5 | DF__NamePrefix__ModifiedDate | DEFAULT_CONSTRAINT | |
| 6 | DF__Password__Active | DEFAULT_CONSTRAINT | |
| 7 | DF__Password__CreatedDate | DEFAULT_CONSTRAINT | |
| 8 | DF__User__Active | DEFAULT_CONSTRAINT | |
| 9 | DF__User__CreatedDate | DEFAULT_CONSTRAINT | |
| 10 | DF__User__ModifiedDate | DEFAULT_CONSTRAINT | |
| 11 | DF__UserContact__CreatedDate | DEFAULT_CONSTRAINT | |
| 12 | DF__UserContactType__Active | DEFAULT_CONSTRAINT | |
| 13 | DF__UserContactType__CreatedDate | DEFAULT_CONSTRAINT | |
| 14 | DF__UserContactType__ModifiedDate | DEFAULT_CONSTRAINT | |
| 15 | DF__UserNameSuffix__Active | DEFAULT_CONSTRAINT | |
| 16 | DF__UserNameSuffix__CreatedDate | DEFAULT_CONSTRAINT | |
| 17 | DF__UserNameSuffix__ModifieDate | DEFAULT_CONSTRAINT | |
| 18 | DF__UserRole__Active | DEFAULT_CONSTRAINT | |
| 19 | DF__UserRole__CreatedDate | DEFAULT_CONSTRAINT | |
| 20 | DF__UserRole__ModifiedDate | DEFAULT_CONSTRAINT | |
| 21 | DF__UserUserNamePrefix__CreatedDate | DEFAULT_CONSTRAINT | |
| 22 | DF__UserUserNameSuffix__CreatedDate | DEFAULT_CONSTRAINT | |
| 23 | DF__UserUserRole__BeginDate | DEFAULT_CONSTRAINT | |
| 24 | DF__UserUserRole__CreatedDate | DEFAULT_CONSTRAINT | |
| 25 | DF__UserUserRole__EndDate | DEFAULT_CONSTRAINT | |
| 26 | DF_User_ExternalUser | DEFAULT_CONSTRAINT | |
| 27 | DF_User_UserGenderID | DEFAULT_CONSTRAINT | |
| 28 | DF_UserGender_CreatedDate | DEFAULT_CONSTRAINT | |
| 29 | DF_UserGender_ModifiedDate | DEFAULT_CONSTRAINT | |
| 30 | CreatedByUserID_Password_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 31 | CreatedByUserID_User_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 32 | CreatedByUserID_UserContact_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 33 | CreatedByUserID_UserContactType_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 34 | CreatedByUserID_UserGender_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 35 | CreatedByUserID_UserNamePrefix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 36 | CreatedByUserID_UserNameSuffix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 37 | CreatedByUserID_UserRole_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 38 | CreatedByUserID_UserUserNamePrefix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 39 | CreatedByUserID_UserUserNameSuffix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 40 | CreatedByUserID_UserUserRole_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 41 | FK_SupervisorUserID_UserID | FOREIGN_KEY_CONSTRAINT | |
| 42 | ModifiedByUserID_User_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 43 | ModifiedByUserID_UserContactType_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 44 | ModifiedByUserID_UserGender_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 45 | ModifiedByUserID_UserNamePrefix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 46 | ModifiedByUserID_UserNameSuffix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 47 | ModifiedByUserID_UserRole_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 48 | User_Password_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 49 | User_UserContact_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 50 | User_UserUserNamePrefix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 51 | User_UserUserNameSuffix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 52 | User_UserUserRole_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 53 | UserContactType_UserContact_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 54 | UserGenderID_User_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 55 | UserNamePrefix_UserUserNamePrefix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 56 | UserNameSuffix_UserUserNameSuffix_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 57 | UserRole_UserUserRole_FK1 | FOREIGN_KEY_CONSTRAINT | |
| 58 | LoginAttempt_PK | PRIMARY_KEY_CONSTRAINT | |
| 59 | Password_PK | PRIMARY_KEY_CONSTRAINT | |
| 60 | PK_UserGender | PRIMARY_KEY_CONSTRAINT | |
| 61 | User_PK | PRIMARY_KEY_CONSTRAINT | |
| 62 | UserContact_PK | PRIMARY_KEY_CONSTRAINT | |
| 63 | UserContactType_PK | PRIMARY_KEY_CONSTRAINT | |
| 64 | UserNamePrefix_PK | PRIMARY_KEY_CONSTRAINT | |
| 65 | UserNameSuffix_PK | PRIMARY_KEY_CONSTRAINT | |
| 66 | UserRole_PK | PRIMARY_KEY_CONSTRAINT | |
| 67 | UserUserNamePrefix_PK | PRIMARY_KEY_CONSTRAINT | |
| 68 | UserUserNameSuffix_PK | PRIMARY_KEY_CONSTRAINT | |
| 69 | UserUserRole_PK | PRIMARY_KEY_CONSTRAINT | |
| Tables |
| Views |
| Functions (UDFs) |
| Constraints |
| Stored Procedures |
| Stored Procedures Back to Top | |||
| Object Name | Object Type | Description | |
All of Conrad Muller's work on this page is free for your use, but no warrenty is stated or implied.