Data Dictionary

Customer
CreditCard
Customer's credit cards
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
creditCardId INTEGER(11) PK NN UNSIGNED     AI
creditCardTypeId INTEGER(10)   NN UNSIGNED      
cardNumber INTEGER(11)   NN        
expiryDate VARCHAR(7)   NN   0000-00 Date the credit card will expire, must be greater than today's date.  
IndexName IndexType Columns
PRIMARY PRIMARY creditCardId


Customer
Customer Profile
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
customerId INTEGER(10) PK NN UNSIGNED     AI
creditCardId INTEGER(11)   NN UNSIGNED      
questionId INTEGER(10)   NN UNSIGNED      
branchId INTEGER(10)   NN UNSIGNED      
postalCodeId INTEGER(10)   NN UNSIGNED      
address1 VARCHAR(50)   NN        
address2 VARCHAR(50)            
created DATETIME   NN   0000-00-00 00:00:00 Date customer was added into the system (defaults to today)  
securityAnswer VARCHAR(40)   NN     The answer to the customers selected security question  
unsubscribed DATETIME       0000-00-00 00:00:00 Date customer selected to unsubscribe from the system  
suspension DATETIME       0000-00-00 00:00:00 Date customer was automatically suspended from the system, customer has to update profile to un-suspend his/herself  
banned DATETIME       0000-00-00 00:00:00 Date customer was banned from logging into the system  
IndexName IndexType Columns
PRIMARY PRIMARY customerId


CustomerBill
Customer bill, shows how much a customer paid at a specific point in time
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
customerBillId INTEGER(10) PK NN UNSIGNED     AI
customerId INTEGER(10)   NN UNSIGNED      
amountDue DOUBLE   NN   0    
billed DATETIME   NN   0000-00-00 00:00:00 Defaults to today's date  
amountPaid DOUBLE   NN   0 Defaults to same amout as amount due  
paid DATETIME   NN   0000-00-00 00:00:00 Defaults to the billed date  
IndexName IndexType Columns
PRIMARY PRIMARY customerBillId


CustomerTransaction
A transaction for which a customer is to be billed
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
customerTransactionId INTEGER(10) PK NN UNSIGNED     AI
rateId INTEGER(10)   NN UNSIGNED      
customerBillId INTEGER(10)   NN UNSIGNED      
customerId INTEGER(10)   NN UNSIGNED      
transactionDate DATETIME   NN   0000-00-00 00:00:00 Transaction start time, defaults to now  
closedDate DATETIME       0000-00-00 00:00:00    
volume DOUBLE         The amount of a service used (eg. pages printed, time/bandwidth used)  
description VARCHAR(45)   NN     Description of service used  
IndexName IndexType Columns
PRIMARY PRIMARY customerTransactionId


InternetUsage
Customer transaction that holds details about an internet connection
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
internetUsageId INTEGER(10) PK NN UNSIGNED     AI
customerTransactionId INTEGER(10)   NN UNSIGNED      
ipAddress VARCHAR(40)   NN     Network IP Address assigned to the customer  
monitorPID VARCHAR(10)   NN     The process ID used in monitoring the traffic for this customer  
forcedClose TINYINT(4)   NN   1 Boolean value of whether the connection was forced closed, defaults to 1 (false)  
IndexName IndexType Columns
PRIMARY PRIMARY internetUsageId


PrintedDocument
Customer transactions for a document that was printed to a cafe printer
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
printedDocumentId INTEGER(10) PK NN UNSIGNED     AI
customerTransactionId INTEGER(10)   NN UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY printedDocumentId


TaxPaid
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
taxPaidId INTEGER PK NN UNSIGNED     AI
customerBillId INTEGER(10)   NN UNSIGNED      
description VARCHAR(15)   NN        
amount DECIMAL            
IndexName IndexType Columns
PRIMARY PRIMARY taxPaidId





User Authentication
UserGroups
Users Groups used for permissions
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
groupId INTEGER(10) PK NN UNSIGNED     AI
groupName VARCHAR(45)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY groupId


PreviousPassword
Previous passwords entered by a user
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
previousPasswordId INTEGER(10) PK NN UNSIGNED     AI
userId INTEGER(10)   NN UNSIGNED      
password VARCHAR(45)   NN     Hashed version of previous password entered  
changeDate DATETIME   NN   0000-00-00 00:00:00 Defaults to now  
IndexName IndexType Columns
PRIMARY PRIMARY previousPasswordId


SecurityQuestion
Customer questions to prove identity in the event they need to reset their password
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
questionId INTEGER(10) PK NN UNSIGNED     AI
question VARCHAR(60)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY questionId


User
Employees and customers that can login to the system
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
userId INTEGER(10) PK NN UNSIGNED     AI
username VARCHAR(15)   NN     Must be unique  
firstName VARCHAR(45)   NN        
lastName VARCHAR(45)   NN        
password VARCHAR(45)   NN     Hashed value of the users password  
passwordExpiry DATETIME   NN   0000-00-00 00:00:00 Date the password will expire, user will then have to re-entere a new password. Defaults to 3 months from now.  
status VARCHAR(45)   NN     Status of account, a message that will be displayed to indicate that they have been locked out of the system by a system administrator  
lastLogin DATETIME   NN   0000-00-00 00:00:00    
IndexName IndexType Columns
PRIMARY PRIMARY userId
username Index username
name Index firstName
lastName


UserGroup
Linking users and groups tables
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
groupId INTEGER(10)   NN UNSIGNED     AI
userId INTEGER(10)   NN UNSIGNED      


SystemAction
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
systemActionId INTEGER PK NN UNSIGNED     AI
groupId INTEGER(10)   NN UNSIGNED      
systemActionCode VARCHAR(10)            
systemActionName VARCHAR(45)            
IndexName IndexType Columns
PRIMARY PRIMARY systemActionId





Reporting
PrintedReport
User values entered on a report that he/she has printed
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
printedReportId INTEGER(10) PK NN UNSIGNED     AI
printedReportParametersId INTEGER(11)   NN        
userId INTEGER(10)   NN UNSIGNED      
lastRun DATETIME   NN   0000-00-00 00:00:00 Date the report was last run by the specific user (defaults to now)  
reportName VARCHAR(45)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY printedReportId


PrintedReportParameters
Parameters for a report that a user has printed
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
printedReportParametersId INTEGER(11) PK NN       AI
attribute VARCHAR(10)   NN     The name of an attribute filled out on a report  
value VARCHAR(90)   NN     The value of an attribute filled out on a report  
IndexName IndexType Columns
PRIMARY PRIMARY printedReportParametersId





Location
City
City Location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
cityId INTEGER(10) PK NN UNSIGNED     AI
provinceId INTEGER(10)   NN UNSIGNED      
cityName VARCHAR(65)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY cityId


Country
Country Location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
countryId INTEGER(10) PK NN UNSIGNED     AI
countryCode VARCHAR(4)   NN     Short form country code used for postage (eg. CA for Canada)  
countryName VARCHAR(45)   NN     Full country name  
IndexName IndexType Columns
PRIMARY PRIMARY countryId


PostalCode
Postal Codes
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
postalCodeId INTEGER(10) PK NN UNSIGNED     AI
postalCode VARCHAR(15)   NN     Postal Code (ZIP or other) used to validate a customers entered address  
IndexName IndexType Columns
PRIMARY PRIMARY postalCodeId


Province
Province location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
provinceId INTEGER(10) PK NN UNSIGNED     AI
countryId INTEGER(10)   NN UNSIGNED      
provinceCode VARCHAR(4)   NN     Province code used in short form (eg. ON for Ontario)  
provinceName VARCHAR(40)   NN     Province full name  
IndexName IndexType Columns
PRIMARY PRIMARY provinceId


Street
Street Location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
streetNameId INTEGER(10) PK NN UNSIGNED     AI
postalCodeId INTEGER(10)   NN UNSIGNED      
cityId INTEGER(10)   NN UNSIGNED      
name VARCHAR(45)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY streetNameId


TaxRate
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
taxRateId INTEGER PK NN UNSIGNED     AI
name VARCHAR(15)            
registrationNumber VARCHAR(20)            
rateCalculation VARCHAR(45)         How to calculate the taxes for a province. Some tax rates are coumpouned, not just a percentage of the sub total.  
IndexName IndexType Columns
PRIMARY PRIMARY taxRateId


ProvinceTaxRate
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
provinceId INTEGER(10)   NN UNSIGNED      
taxRateId INTEGER   NN UNSIGNED      





Branch
Branch
Cafe Branch Location
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
branchId INTEGER(10) PK NN UNSIGNED     AI
postalCodeId INTEGER(10)   NN UNSIGNED      
branchName VARCHAR(45)   NN        
address1 VARCHAR(45)   NN        
address2 VARCHAR(45)            
added DATETIME   NN   0000-00-00 00:00:00    
removed DATETIME       0000-00-00 00:00:00 If a branch is sold, new user/transaction cannot be added referencing it  
IndexName IndexType Columns
PRIMARY PRIMARY branchId


SystemParameters
System parameters applied to each of the branch locations
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
systemParametersId INTEGER(10) PK NN UNSIGNED     AI
branchId INTEGER(10)   NN UNSIGNED      
maxConnectionTime INTEGER(11)   NN   0 The maximum amount of time, in minutes, that a customer can stay connected simutaniously (0 for unlimited)  
maxBandwidthUse INTEGER(11)   NN   0 The maximum amount of data, in kilobytes, that a customer can transfer before being disconnected (0 for unlimited)  
suspendAccountTime INTEGER(11)   NN   0 The maximum number of days between logins before a customer is moved to a suspended state (0 for unlimited)  
maxIdleTime INTEGER(11)   NN   0 The maximum amount of time, in minutes, that a customer can be idle on the network before being disconnected (0 for unlimited)  
IndexName IndexType Columns
PRIMARY PRIMARY systemParametersId





Misc
CreditCardType
Credit Card Types (NOT EDITABLE in the system)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
creditCardTypeId INTEGER(10) PK NN UNSIGNED     AI
description VARCHAR(20)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY creditCardTypeId


Rate
Rate charged to customers for services
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
rateId INTEGER(10) PK NN UNSIGNED     AI
unitId INTEGER(10)   NN UNSIGNED      
description VARCHAR(45)   NN        
amount DOUBLE   NN   .0    
unitQuantity INTEGER(11)   NN   1    
effectiveDate DATETIME   NN   0000-00-00 00:00:00 Date this rate comes into effect  
withdrawnDate DATETIME   NN   0000-00-00 00:00:00 Date this rate can no longer be used  
IndexName IndexType Columns
PRIMARY PRIMARY rateId


SiteVisited
Record of a web site that was visited and when
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
siteVisitedId INTEGER(10) PK NN UNSIGNED     AI
branchId INTEGER(10)   NN UNSIGNED      
visited DATETIME   NN   0000-00-00 00:00:00 Defaults to now  
url VARCHAR(45)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY siteVisitedId


Unit
Unit of measurement for rates
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
unitId INTEGER(10) PK NN UNSIGNED     AI
description VARCHAR(45)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY unitId





System Messaging
SystemMessage
An event that occured in the system
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
systemMessageId INTEGER(10) PK NN UNSIGNED     AI
locale VARCHAR(5)   NN        
systemMessageDescriptionId INTEGER(10)   NN        
userId INTEGER(10)   NN UNSIGNED      
description VARCHAR(45)   NN     Additional description beyond the system message description  
occurred DATETIME   NN   0000-00-00 00:00:00 Defaults to now  
IndexName IndexType Columns
PRIMARY PRIMARY systemMessageId


SystemMessageClass
The class from which the system message occured
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
systemMessageClassId INTEGER(10) PK NN UNSIGNED     AI
description VARCHAR(20)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY systemMessageClassId


SystemMessageDescription
A description of the error, what caused it, and how to fix it
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
systemMessageDescriptionId INTEGER(10) PK NN UNSIGNED     AI
locale VARCHAR(5) PK NN     Help message language  
messageTypeId INTEGER(11)   NN        
systemMessageClassId INTEGER(11)   NN        
description VARCHAR(45)   NN     A description of the error (Name is too long)  
cause VARCHAR(45)   NN     A description of what caused the error (The name entered into the system is too long)  
resolution VARCHAR(45)   NN     A description of how to correct the error (Enter a name that is between 3 and 15 charaters)  
IndexName IndexType Columns
PRIMARY PRIMARY systemMessageDescriptionId


SystemMessageType
Differentiates from types of system messages (errors, exceptions, input errors, etc)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
messageTypeId INTEGER(11) PK NN UNSIGNED     AI
systemMessageCode VARCHAR(4)   NN     Short code that describes a message (eg. ERR for error, EXC for system exception, etc)  
description VARCHAR(45)   NN     Long description to the error  
IndexName IndexType Columns
PRIMARY PRIMARY messageTypeId





Hosted, with thanks on SourceForge