DataValidation.md

1. User Table
2. Employee Table
3. Member Table
4. Friendship Table
5. Events Table
6. Address Table
7. Order Table
8. OrderItem Table
9. Game Table
10. Platform Table
11. Category Table
12. Review Table

User Table

Column Name	Data Type	Validation Rule	Notes
userId	int	10,000,000 -> 19,999,999 NOT NULL
email	string	[EMAIL] NOT NULL
displayName	string	Characters (4, 50) NOT NULL
gender	string	M, F or O NOT NULL
firstName	string	Characters (1, 50) NOT NULL
lastName	string	Characters (1, 50) NOT NULL
dateOfBirth	dateTime	NOT NULL, Not in future
registrationDate	dateTime	No Validation	Generated by server
phoneNumber	string	[PHONE] NOT NULL	Validated by ASP Regex

Employee Table

Column Name	Data Type	Validation Rule	Notes
employeeId	int	200,000 -> 299,999

Member Table

Column Name	Data Type	Validation Rule	Notes
memberId	int	30,000,000 -> 39,999,999 NOT NULL
stripeId	string	Out Of Scope
isVerified	boolean	NOT NULL, DEFAULT FALSE	Generated by server
prefPlatformId	int	FK Platform Table
prefCategoryId	int	FK Category Table

Friendship Table

Column Name	Data Type	Validation Rule	Notes
friendeeID	int	FK User Table
frienderID	int	FK User Table
isFamilyMember	boolean	NOT NULL, DEFAULT FALSE
isAccepted	boolean	NOT NULL, DEFAULT FALSE

Events Table

Column Name	Data Type	Validation Rule	Notes
eventId	int	4,000,000 -> 4,999,999, NOT NULL
employeeId	int	FK Employee Table
location	string	Characters (0, 2000)
startDate	dateTime	Not in the past, NOT NULL
endDate	dateTime	Not before startDate, NOT NULL
description	string	Characters (0, 4000)
capacity	int	0 < VAL < Int(MAX), NOT NULL

Address Table

Column Name	Data Type	Validation Rule	Notes
addressId	int	500,000,000 -> 599,999,999, NOT NULL
memberId	int	FK Members Table
address	string	Char (1, 255), NOT NULL	Could validate against Canada Post API
city	string	Char (2, 50), NOT NULL
country	string	Char (2, 50), NOT NULL
postalCode	string	REGEX, NOT NULL

Order Table

Column Name	Data Type	Validation Rule	Notes
orderId	int	600,000,000 -> 699,999,999, NOT NULL
memberId	int	FK Members Table NOT NULL
processorId	int	FK Employees Table	Set after checkout
billingAddressID	int	FK Address Table	Set at checkout
shippingAddressID	int	FK Address Table	Set at checkout
orderPlacementDate	dateTime	System Generated DEFAULT( NOW )	Set at checkout
shipDate	dateTime	Not before order placement	Set at shipped
isProcessed	boolean	NOT NULL, DEFAULT FALSE	Set after checkout

OrderItem Table

Column Name	Data Type	Validation Rule	Notes
gameID	int	FK Game Table
orderID	int	FK Order Table
salePrice	decimal	NOT NEGATIVE NOT NULL	Canadian Dollars

Game Table

Column Name	Data Type	Validation Rule	Notes
gameID	int	7,000,000 -> 7,999,999, NOT NULL
platformID	int	FK Platform Table, NOT NULL
name	string	Characters (1, 50), NOT NULL
releaseDate	dateTime	NOT NULL
suggestedRetailPrice	decimal	NOT NEGATIVE, NOT NULL
publisher	string	Characters (1, 50, NOT NULL

Platform Table

Column Name	Data Type	Validation Rule	Notes
platformId	int	800 -> 899 NOT NULL
name	string	Characters (1, 50) NOT NULL

Category Table

Column Name	Data Type	Validation Rule	Notes
categoryId	int	8,000 -> 8,999 NOT NULL
name	string	Characters (1, 50) NOT NULL

Review Table

Column Name	Data Type	Validation Rule	Notes
reviewId	int	900,000,000 -> 999,999,999
gameId	int	FK Games NOT NULL
memberId	int	FK Members Table NOT NULL
approverId	int	FK Employees Table	Null if not yet Assessed
rating	float	0 < VALUE < 1 NOT NULL
subjectText	string	Characters (1, 500)	Null if just a rating
contextText	string	Characters (0, 4000)	Null if just a rating
isApproved	boolean	NOT NULL, DEFAULT FALSE