New Purchase System Design

Database

<div id="_mcePaste">CREATE TABLE `accounts` (   `AccountName` varchar(20),   `Email` varchar(50),   `Password` varchar(30),   `First` varchar(30),   `Last` varchar(30),   `Phone` varchar(11),   `OrgAccount` varchar(20),   `OrgName` varchar(50),   `Type` enum(user,coach,organization,
admin),   `CreationTimestamp` timestamp,   PRIMARY KEY (`AccountName`) );</div>
<div id="_mcePaste">CREATE TABLE `account_inventories` (   `AccountName` varchar(20),   `IID` varchar(10),   `Pin` varchar(6),   `ProductCode` varchar(20),   `NickName` varchar(20),   PRIMARY KEY (`AccountName`, `IID`) );</div>


CREATE TABLE `coupons` (   `Code` varchar(15),   `OrgName` varchar(20),   `Description` varchar(40),   `UseCount` int,   `UseLimit` int,   `ProductCode` varchar(20),   `Enabled` boolean,   `ExpirationDate` date,   `Discount` int,   PRIMARY KEY (`Code`),   FOREIGN KEY (`OrgName`),   FOREIGN KEY (`ProductCode`) );



CREATE TABLE `meta_inventories` (   `IID` varchar(10),   `OrderId` varchar(255),   `ProductCode` varchar(20),   `Comment` varchar(255),   PRIMARY KEY (`IID`) );



CREATE TABLE `orders` (   `OrderID` varchar(255),   `AccountName` varchar(20),   `OrderUpdateTimestamp` timestamp,   `OrderStatus` varchar(255),   `OrderTotal` decimal,   `TxnID` varchar(255),   `PaypalEmail` varchar(255),   `CouponCode` varchar(15),   `OrgName` varchar(20),   PRIMARY KEY (`OrderID`),   FOREIGN KEY (`CouponCode`) ); CREATE TABLE `order_purchases` (   `OrderID` varchar(255),   `ProductCode` varchar(20),   `Quantity` int,   `TemplateName` char(10),   PRIMARY KEY (`OrderID`, `ProductCode`),   FOREIGN KEY (`TemplateName`) ); CREATE TABLE `Templates` (   `TemplateName` varchar(20),   `TemplateDescription` vachar(1024),   `CompatableProduct` varchar(20),   `TemplateID` char(10),   `OrgAccount` varchar(20),   PRIMARY KEY (`TemplateName`),   FOREIGN KEY (`CompatableProduct`, `TemplateID`),   FOREIGN KEY (`OrgAccount`) ); CREATE TABLE `ActiveProduct` (   `Product` varchar(20),   `TemplateName` varchar(20),   PRIMARY KEY (`Product`, `TemplateName`) );





Organization

Organization.png

Coach

Coach.png

User

User.png

Topic attachments
I Attachment Action Size Date Who Comment
Pngpng Coach.png manage 31.8 K 2016-05-04 - 13:58 JimSkon  
Pngpng Organization.png manage 63.8 K 2016-05-04 - 13:57 JimSkon  
Pngpng User.png manage 43.0 K 2016-05-04 - 13:58 JimSkon  
Topic revision: r2 - 2016-06-15 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback