· 8 years ago · Jan 04, 2018, 11:06 AM
1-- ----------------------------
2-- Table structure for `customers`
3-- ----------------------------
4DROP TABLE IF EXISTS `customers`;
5CREATE TABLE `customers` (
6 `customerID` varchar(32) NOT NULL,
7 `name1` varchar(128) DEFAULT NULL,
8 `name2` varchar(128) DEFAULT NULL,
9 `name3` varchar(128) DEFAULT NULL,
10 `cchash` varchar(16) DEFAULT NULL,
11 `expiry` timestamp,
12 PRIMARY KEY (`customerID`)
13) ENGINE=MyISAM DEFAULT CHARSET=utf8;
14
15-- ----------------------------
16-- Table structure for `employees`
17-- ----------------------------
18DROP TABLE IF EXISTS `employees`;
19CREATE TABLE `employees` (
20 `employeeID` varchar(32) NOT NULL,
21 `bID` varchar(32) NOT NULL, -- Which branch the employee works at
22 `name1` varchar(128) NOT NULL DEFAULT,
23 `name2` varchar(128) NOT NULL DEFAULT,
24 `name3` varchar(128) NOT NULL DEFAULT,
25 PRIMARY KEY (`employeeID`),
26 KEY `sID` (`storeID`)
27) ENGINE=MyISAM DEFAULT CHARSET=utf8;
28
29-- ----------------------------
30-- Table structure for `menu`
31-- ----------------------------
32DROP TABLE IF EXISTS `menu`;
33CREATE TABLE `menu` (
34 `menuID` varchar(32) NOT NULL,
35 `title` varchar(128) NOT NULL,
36 `price` double(16,0) NOT NULL,
37 `expires` timestamp,
38 `sID` varchar(32) NOT NULL, -- which station the item is to be prepared at
39 `type` binary(8) DEFAULT NULL,
40 -- This is so you can use a bitfield to show various types of menu options
41 -- like
42 -- 0100100111
43 -- ^drink ^part of combo (and so on)
44 -- basically just allowing for a lot of permutations that can be
45 -- represented by base 10 or whatever you want really
46
47 PRIMARY KEY (`menuID`)
48) ENGINE=MyISAM DEFAULT CHARSET=utf8;
49
50-- ----------------------------
51-- Table structure for `orders`
52-- ----------------------------
53DROP TABLE IF EXISTS `orders`;
54CREATE TABLE `orders` (
55 `orderID` varchar(32) NOT NULL,
56 `bID` varchar(32) NOT NULL, -- which branch this was purchased at
57 `orderTimeStarted` timestamp NOT NULL, -- can be set to match the first receipt started timestamp
58 `orderTimeFinished` timestamp DEFAULT NULL, -- can be set to match the last receipt finished timestamp
59 `rID` varchar(32) DEFAULT NULL, -- in case we have an itemized receipt we want to list
60 `cID` varchar(32) DEFAULT NULL,-- in case the person isn't anonymous (cash)
61 `price` double(32,0) DEFAULT NULL,
62 `paymentType` enum('golden dubloons','debit','amex','discover','visa','mastercard','cash') DEFAULT NULL,
63 PRIMARY KEY (`orderID`,`sID`),
64 KEY `sID` (`storeID`),
65 KEY `rID` (`receiptID`),
66 KEY `cID` (`customerID`)
67) ENGINE=MyISAM DEFAULT CHARSET=utf8;
68
69-- ----------------------------
70-- Table structure for `receipts` (aka transaction item)
71-- ----------------------------
72DROP TABLE IF EXISTS `receipts`;
73CREATE TABLE `receipts` (
74 `receiptID` varchar(32) NOT NULL,
75 `oID` varchar(32) NOT NULL, -- order the receipt is linked to
76 `timeStarted` timestamp NOT NULL, -- when the item was started
77 `timeCompleted` timestamp DEFAULT NULL, -- when the item was finished
78 `sID` varchar(32) NOT NULL, -- which station it was prepared at
79 `eID` varchar(32) NOT NULL, -- which employeed prepared the specific item (or handled it)
80 `mID` varchar(32) NOT NULL, -- link to the menu item
81 `price` double(32,0) NOT NULL, -- just in case it got itemized as discounted or part of a combo
82 PRIMARY KEY (`receiptID`,`oID`),
83 KEY `oID` (`orderID`),
84 KEY `sID` (`stationID`),
85 KEY `eID` (`employeeID`),
86 KEY `mID` (`menuID`)
87) ENGINE=MyISAM DEFAULT CHARSET=utf8;
88
89-- ----------------------------
90-- Table structure for `stations`
91-- ----------------------------
92DROP TABLE IF EXISTS `stations`;
93CREATE TABLE `stations` (
94 `stationID` varchar(32) NOT NULL,
95 `name` varchar(128) NOT NULL, -- station name, allows for more granularity than just equipment type
96 `equipment` enum('cash register','deep fryer','drink','sandwich prep','drivethru payment','etc...') DEFAULT NULL,
97 PRIMARY KEY (`stationID`),
98) ENGINE=MyISAM DEFAULT CHARSET=utf8;
99
100-- ----------------------------
101-- Table structure for `shifts`
102-- This goes nothing to say about the application logic that would enforce the shifts, this is more for
103-- tracking the state of which employee is at which station for which durations
104-- ----------------------------
105DROP TABLE IF EXISTS `shifts`;
106CREATE TABLE `shifts` (
107 `shiftID` varchar(32) NOT NULL,
108 `eID` varchar(32) NOT NULL, -- employee who worked the shift
109 `sID` varchar(32) NOT NULL, -- which station the employee checked in at
110 `clockIN` timestamp NOT NULL, -- when the employee clocked in
111 `clockOUT` timestamp DEFAULT NULL, -- when the employee clocked out
112 PRIMARY KEY (`shiftID`)
113) ENGINE=MyISAM DEFAULT CHARSET=utf8;
114
115-- ----------------------------
116-- Table structure for `branches`
117-- ----------------------------
118DROP TABLE IF EXISTS `branches`;
119CREATE TABLE `branches` (
120 `branchID` varchar(32) NOT NULL,
121 `name` varchar(128) NOT NULL, -- branch name, ie "TheHotList Hot Sandwiches"
122 `address1` varchar(128) NOT NULL,
123 `address2` varchar(128) DEFAULT NULL,
124 `address3` varchar(128) DEFAULT NULL,
125 `city` varchar(128) DEFAULT NULL,
126 `state` varchar(2) DEFAULT NULL, -- state code only
127 `phone` varchar(16) DEFAULT NULL, -- just in case the future has us using IPv6 addresses instead of phone numbers
128 PRIMARY KEY (`branchID`),
129) ENGINE=MyISAM DEFAULT CHARSET=utf8;