· 8 years ago · Jan 04, 2018, 10:20 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 `bID` (`branchID`)
27) ENGINE=MyISAM DEFAULT CHARSET=utf8;
28
29-- ----------------------------
30-- Table structure for `menuitems` (a menu is nothing but a query for menu items)
31-- ----------------------------
32DROP TABLE IF EXISTS `menuitems`;
33CREATE TABLE `menuitems` (
34 `menuitemsID` varchar(32) NOT NULL,
35 `title` varchar(128) NOT NULL,
36 `price` double(16,0) NOT NULL,
37 `expires` timestamp DEFAULT NULL,
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 PRIMARY KEY (`menuitemsID`),
47 KEY `bID` (`branchID`)
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 `cID` varchar(32) DEFAULT NULL, -- linked customer in case the person isn't anonymous
60 `price` double(32,0) DEFAULT NULL, -- final bill (sanity check to add up all the order items)
61 `paymentType` enum('golden dubloons','debit','amex','discover','visa','mastercard','cash') DEFAULT NULL,
62 PRIMARY KEY (`orderID`,`bID`),
63 KEY `bID` (`branchID`),
64 KEY `rID` (`receiptID`),
65 KEY `cID` (`customerID`)
66) ENGINE=MyISAM DEFAULT CHARSET=utf8;
67
68-- ----------------------------
69-- Table structure for `orderitem`
70-- ----------------------------
71DROP TABLE IF EXISTS `orderitem`;
72CREATE TABLE `orderitem` (
73 `orderitemID` varchar(32) NOT NULL, -- normally I try to avoid similar looking varnames like oid and oiid etc...
74 `oID` varchar(32) NOT NULL, -- order the receipt is linked to
75 `timeStarted` timestamp NOT NULL, -- when the item was started
76 `timeCompleted` timestamp DEFAULT NULL, -- when the item was finished
77 `sID` varchar(32) NOT NULL, -- which station it was prepared at
78 `eID` varchar(32) NOT NULL, -- which employeed prepared the specific item (or handled it)
79 `mID` varchar(32) NOT NULL, -- link to the menu item
80 `price` double(32,0) NOT NULL, -- just in case it got itemized as discounted or part of a combo
81 PRIMARY KEY (`orderitemID`,`oID`),
82 KEY `oID` (`orderID`),
83 KEY `sID` (`stationID`),
84 KEY `eID` (`employeeID`),
85 KEY `mID` (`menuID`)
86) ENGINE=MyISAM DEFAULT CHARSET=utf8;
87
88-- ----------------------------
89-- Table structure for `stations`
90-- ----------------------------
91DROP TABLE IF EXISTS `stations`;
92CREATE TABLE `stations` (
93 `stationID` varchar(32) NOT NULL,
94 `bID` varchar(32) NOT NULL, -- ties the physical assets to a location (branch)
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`,`bID`),
98 KEY `bID` (`branchID`)
99) ENGINE=MyISAM DEFAULT CHARSET=utf8;
100
101-- ----------------------------
102-- Table structure for `shifts`
103-- This goes nothing to say about the application logic that would enforce the shifts, this is more for
104-- tracking the state of which employee is at which station for which durations
105-- ----------------------------
106DROP TABLE IF EXISTS `shifts`;
107CREATE TABLE `shifts` (
108 `shiftID` varchar(32) NOT NULL,
109 `eID` varchar(32) NOT NULL, -- employee who worked the shift
110 `sID` varchar(32) NOT NULL, -- which station the employee checked in at
111 `clockIN` timestamp NOT NULL, -- when the employee clocked in
112 `clockOUT` timestamp DEFAULT NULL, -- when the employee clocked out
113 PRIMARY KEY (`shiftID`)
114) ENGINE=MyISAM DEFAULT CHARSET=utf8;
115
116-- ----------------------------
117-- Table structure for `branches`
118-- ----------------------------
119DROP TABLE IF EXISTS `branches`;
120CREATE TABLE `branches` (
121 `branchID` varchar(32) NOT NULL,
122 `name` varchar(128) NOT NULL, -- branch name, ie "TheHotList Hot Sandwiches"
123 `address1` varchar(128) NOT NULL,
124 `address2` varchar(128) DEFAULT NULL,
125 `address3` varchar(128) DEFAULT NULL,
126 `city` varchar(128) DEFAULT NULL,
127 `state` varchar(2) DEFAULT NULL, -- state code only
128 `phone` varchar(16) DEFAULT NULL, -- just in case the future has us using IPv6 addresses instead of phone numbers
129 PRIMARY KEY (`branchID`),
130) ENGINE=MyISAM DEFAULT CHARSET=utf8;