Database Schema and Validation
Login Demo Home | Log In | Register | Reset Password
Database Structure
| Field Name | Type | Notes | Validation |
|---|---|---|---|
| ID | autonumber | Internal reference ID number for relationships. | ^[[:digit:]]{1,32}$ - Needed only if ID is ever submitted in a form or query string. |
| credential | char(32) | A programmatically generated md5 hash used to uniquely identify the individual, and is specifically used to set a cookie verifying that the member has logged on. It is resistant to spoofing because of its length and randomness, but to further strengthen this, the programs should set a validation code which is a hash of another field, the choice of which will depend on how the business owners wants a session to behave. For instance, if a session never expires, a hash of the last login can be used. If a session never expires and can span several computers, the registration date can be used. For higher security, the session should be programmatically set to expire after either a given time based on last login or last access. The credential and validation code should be deleted or overwritten when the session expires (it is probably safest to overwrite with random characters and set to immediately expire because one cannot guarantee how a browser client will handle a cookie). If sessions are defined to happen only on one browser client, then the credential field should change with every log in, log out, and session expiration (if possible). | ^[[:alnum:]]{32}$ - exactly 32 letters and numbers, mixed case. |
| title | varchar(4) | Dr, Miss, Mr, Mrs, or Ms. If the business wants to store a period in the database, the length and validation should be appropriately changed. | ^[[:alnum:]]{2,4}$ - 2-4 letters and numbers, mixed case. |
| firstName | varchar(25) | Using 1990 Census data, the longest first names are 11 characters in length. Assuming that some people go by two, hyphenated names (e.g., Betty-Lue), it is theoretically possible to have 22 characters plus a hyphen or space, so I have set the field at 25. | ^[[:alnum:]|-| ]{1,25}$ |
| middleName | varchar(25) | Similar to the logic for firstName. The field can be used to only store the middle initial if desired. | ^[[:alnum:]|-| ]{1,25}$ |
| lastName | varchar(30) | Using the 1990 census data, the longest last names are 13 characters. Hyphenated last names are quite common now, so it is theoretically possible to have 26 letters and a dash, so I have set the field length to 30. | ^[[:alnum:]|-| ]{1,30}$ |
| varchar(255) | Technically, according to RFC 2821, the maximum length for the "local part" (a.k.a. the username) is 64 characters, and the maximum length for the domain name or number is 255 characters. Along with the @ sign, this gives a maximum length of 320 characters. Businesses may consider 255 more practical, but 320 will be guaranteed robust.
If a duplicate is found, the member should be given the option to log in or reset/recover their password via e-mail. To guarantee that an e-mail account is valid, the business owner may opt to send a generated password to the e-mail address rather than have the member enter one during registration. |
^([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x22([^\x0d\x22\x5c\x80-\xff]|\x5c\x00-\x7f)*\x22)(\x2e([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x22([^\x0d\x22\x5c\x80-\xff]|\x5c\x00-\x7f)*\x22))*\x40([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x5b([^\x0d\x5b-\x5d\x80-\xff]|\x5c\x00-\x7f)*\x5d)(\x2e([^\x00-\x20\x22\x28\x29\x2c\x2e\x3a-\x3c\x3e\x40\x5b-\x5d\x7f-\xff]+|\x5b([^\x0d\x5b-\x5d\x80-\xff]|\x5c\x00-\x7f)*\x5d))*$ Use isEmail() function, which covers all the specifications for a valid e-mail address. Simply checking for the @ sign is not enough to ensure a validly formed e-mail address. |
|
| username | varchar(16) | Should always be stored lower-case in the database, and compared in a case-insensitive manner. If a duplicate is found, the member should be given the option to log in or reset/recover their password via e-mail. | ^[[:alnum:]]{6,16}$ |
| passwordHash | char(32) | The password is hashed before being stored in this field. On subsequent logins, the hash of the entered password is compared with the hashed version in the database. If someone manages hijacks the database, no passwords are revealed. Password is case sensitive. The business owner may consider making passwords hard to guess by requiring letters, numbers, and punctuation (special care should be taken if checking for HTML entities, since this will change the punctuation signature). | ^[[:alnum:][:punct:]]{6,16}$ |
| companyName | varchar(100) | I have chosen an arbitrary field length. | ^[[:alnum:][:punct:] ]{1,100}$ |
| address | varchar(100) | I have chosen an arbitrary field length. | ^[[:alnum:][:punct:] ]{1,100}$ |
| address2 | varchar(100) | I have chosen an arbitrary field length. | ^[[:alnum:][:punct:] ]{1,100}$ |
| city | varchar(30) | The longest city names in the United States is 28 characters long according to the post office database I have. Shortest city??? City with numbers??? | ^[[:alnum:] ]{1,30}$ |
| state | varchar(25) | The longest state name in the United States is 14, but the longest Canadian province name is 25 characters. Two letter abbreviations??? | ^[[:alnum:] ]{2,25}$ |
| zipCode | varchar(10) | United States zip codes are either 5 or 9 characters with a dash (10 characters total). Canadian postal codes are six characters. | ^[[:alnum:]- ]{5,10}$ |
| country | char(2) or varchar(44) | Using the ISO 3166 alpha-2 code list. The ISO Maintenance Agency charges a fee for using their abbreviations in commercial products, so one might want to forgo that cost and have a larger field for storage. The longest country name in the ISO list is 44 characters. | ^[[:upper:]]{2}$ or ^[[:alpha:] ]{4,44}$ |
| phoneNumber | char(14) | US and Canadian phone numbers with area codes are usually formatted 999-999-9999 or (999) 999-9999. United Kingdom phone numbers are 999 99999999. Since this database assumes an international model, the number should be stored the way the user entered it or with all punctuation stripped. Since letters are sometimes used to correspond to digits, the phone number should be stored in lower or upper case, depending on what the business desires. | ^[[:alnum:]- ]{10,14}$ |
| faxNumber | char(13) | See phoneNumber notes. | ^[[:alnum:]- ]{10,14}$ |
| registrationDate | timestamp | When the member registered. It is set once and never changed again. If programming allows, the date should always be stored in UTC format. The interface can usually be modeled to adjust for a given time zone. One might also consider adding a time zone field that the member can select, if appropriate. | Controlled internally. No need for validation. |
| lastLogin | date-time | When the member last logged in. | Controlled internally. No need for validation. |
| lastAccessed | date-time | Depending on the needs of the business, this field can be updated with every new page access. Note that if more extensive activity logs are needed, a separate table should be set up. | Controlled internally. No need for validation. |
