| 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 | |
Only the major foriegn key constraints are shown in this diagram.
All of Conrad Muller's work on this page is free for your use, but no warrenty is stated or implied.