· 5 years ago · Oct 11, 2020, 09:12 PM
1
2DROP DATABASE IF EXISTS wavv;
3CREATE DATABASE wavv;
4USE wavv;
5
6CREATE TABLE `user` (
7 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
8 `firstName` VARCHAR(50) NOT NULL COMMENT 'First name of the user',
9 `lastName` VARCHAR(50) NOT NULL COMMENT 'Last name of the user',
10 `isAdmin` BOOL NOT NULL DEFAULT 0 COMMENT 'Boolean field that indicate if user is admin or not',
11 PRIMARY KEY (`id`)
12) COMMENT 'Users and Administrators of wavv';
13
14CREATE TABLE `userContact` (
15 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
16 `userId` BINARY(16) NOT NULL COMMENT 'user FK',
17 `contactType` VARCHAR(50) NOT NULL COMMENT 'The contact type of the user. Facebook, twitter, email...',
18 `contact` VARCHAR(50) NOT NULL COMMENT 'Last name of the user',
19 PRIMARY KEY (`id`)
20) COMMENT 'Contacts of users';
21
22CREATE TABLE `newsletterSubscriptions` (
23 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
24 `userId` BINARY(16) NOT NULL COMMENT 'user FK',
25 `email` VARCHAR(50) NOT NULL COMMENT 'Email of user which will be used to send the newsletter',
26 `subscribedAt` DATETIME NOT NULL COMMENT 'When user subscribed to the newsletter',
27 `unsubscribedAt` DATETIME NULL COMMENT 'When user unsubscribed to the newsletter',
28 PRIMARY KEY (`id`)
29) COMMENT 'Subscriptions to newsletter';
30
31CREATE TABLE `userCredential` (
32 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
33 `userId` BINARY(16) NOT NULL COMMENT 'user FK',
34 `email` VARCHAR(16) NOT NULL COMMENT 'Email',
35 `password` BINARY(16) NOT NULL COMMENT 'Password',
36 `magicHash` BINARY(16) NULL COMMENT 'Used to login by link send to email',
37 `expireAt` DATETIME NULL COMMENT 'It will be null for regular users but mandatory for admin users',
38 PRIMARY KEY (`id`)
39) COMMENT 'User credentials to login';
40
41CREATE TABLE `userToken` (
42 `id` BINARY(16) NOT NULL COMMENT 'Token used by API to validate each request',
43 `userId` BINARY(16) NOT NULL COMMENT 'User the token belong',
44 `expiration` DATETIME NOT NULL COMMENT 'Expiration date and time for the token, every successful request will update the token.',
45 PRIMARY KEY (`id`)
46) COMMENT 'Tokens generated by users to access the api';
47
48CREATE TABLE `userAddress` (
49 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
50 `userId` BINARY(16) NOT NULL,
51 `addressId` BINARY(16) NOT NULL,
52 PRIMARY KEY (`id`)
53) COMMENT 'The addresses of users';
54
55CREATE TABLE `store` (
56 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
57 `keyName` VARCHAR(30) NOT NULL COMMENT 'Unique key with no spaces to identify the store used in the url',
58 `name` VARCHAR(40) NOT NULL COMMENT 'Name of the store',
59 `addressId` BINARY(16) NOT NULL COMMENT 'address FK',
60 PRIMARY KEY (`id`)
61) COMMENT 'A store which want to sell things in our marketplace, stores will be registered by admins.';
62
63CREATE TABLE `storeReview` (
64 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
65 `storeId` BINARY(16) NOT NULL COMMENT 'store FK',
66 `reviewId` BINARY(16) NOT NULL COMMENT 'review FK',
67 PRIMARY KEY (`id`)
68) COMMENT 'Reviews about the stores.';
69
70CREATE TABLE `storeMessage` (
71 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
72 `messageParentId` BINARY(16) NOT NULL COMMENT 'message FK',
73 `message` VARCHAR(255) NOT NULL COMMENT 'message',
74 `sender` BINARY(16) NOT NULL COMMENT 'user FK',
75 `receiver` BINARY(16) NOT NULL COMMENT 'store FK',
76 `sentAt` DATETIME NOT NULL COMMENT 'When message was sent',
77 `readAt` DATETIME NOT NULL COMMENT 'When message was read',
78 PRIMARY KEY (`id`)
79) COMMENT 'Messages that users can send to stores';
80
81CREATE TABLE `userStore` (
82 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
83 `userId` BINARY(16) NOT NULL COMMENT 'user FK',
84 `storeId` BINARY(16) NOT NULL COMMENT 'store FK',
85 PRIMARY KEY (`id`)
86) COMMENT 'Users associated to the store.';
87
88CREATE TABLE `brand` (
89 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
90 `keyName` VARCHAR(30) NOT NULL COMMENT 'Unique key with no spaces to identify the brand used in the url',
91 `name` BINARY(16) NOT NULL COMMENT 'Name of the brand',
92 `about` TEXT NOT NULL COMMENT 'A brief comment about the brand',
93 PRIMARY KEY (`id`)
94) COMMENT 'Brands, it will include also national brands and also small brands like pedal makers, registered only by admins';
95
96CREATE TABLE `userBrand` (
97 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
98 `userId` BINARY(16) NOT NULL COMMENT 'user FK',
99 `brandId` BINARY(16) NOT NULL COMMENT 'store FK',
100 PRIMARY KEY (`id`)
101) COMMENT 'Users associated to the brand, they will be able to edit brand information.';
102
103CREATE TABLE `product` (
104 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
105 `brandId` BINARY(16) NOT NULL COMMENT 'brand FK',
106 `details` TEXT NOT NULL COMMENT 'Details about the product, this field must support markdown',
107 `title` VARCHAR(40) NOT NULL COMMENT 'Product title',
108 `model` VARCHAR(40) NOT NULL COMMENT 'Product model',
109 `year` SMALLINT UNSIGNED NOT NULL COMMENT 'Year the product was released',
110 `isHandmade` BOOL NOT NULL DEFAULT 0 COMMENT 'Indicates if product is handmade',
111 PRIMARY KEY (`id`)
112) COMMENT 'Products pre registered on the system to make it easy to register new items';
113
114CREATE TABLE `productReview` (
115 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
116 `productId` BINARY(16) NOT NULL COMMENT 'product FK',
117 `reviewId` BINARY(16) NOT NULL COMMENT 'review FK',
118 PRIMARY KEY (`id`)
119) COMMENT 'Comments about the producs';
120
121CREATE TABLE `item` (
122 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
123 `productId` BINARY(16) NULL COMMENT 'product FK',
124 `brandId` BINARY(16) NULL COMMENT 'brand FK',
125 `title` VARCHAR(40) NOT NULL COMMENT 'The title of the product',
126 `description` VARCHAR(40) NOT NULL COMMENT 'The description with details about the item',
127 `condition` TINYINT NOT NULL COMMENT '1 New; 2 Mint; 3 Excellent; 4 Very Good; 5 Good; 6 Fair; 7 Poor; 8 Not functioning',
128 `soldAt` DATETIME NULL COMMENT 'Indicate date and time when item was sold. It will be NULL if item is still available.',
129 `price` DECIMAL(10,2) NOT NULL COMMENT 'Price of the item',
130 `oldPrice` DECIMAL(10,2) NOT NULL COMMENT 'Last price of the product, used to display promotions',
131 `isFreeShipping` BOOL NOT NULL COMMENT 'Indicate if the item will have no shipping cost to buyer',
132 `isAcceptingOffers` BOOL NOT NULL COMMENT 'Indicate if the item will accept offers',
133 PRIMARY KEY (`id`)
134) COMMENT 'Items or services to be sold';
135
136CREATE TABLE `itemOffer` (
137 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
138 `itemId` BINARY(16) NULL COMMENT 'item FK',
139 `userId` BINARY(16) NULL COMMENT 'user FK',
140 `price` DECIMAL(10,2) NOT NULL COMMENT 'How much is the offer',
141 `message` TEXT NULL COMMENT 'Message to seler',
142 PRIMARY KEY(`id`)
143) COMMENT 'Offers on items made by users';
144
145CREATE TABLE `itemPriceHistory` (
146 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
147 `productId` BINARY(16) NULL COMMENT 'product FK',
148 `itemId` BINARY(16) NULL COMMENT 'item FK',
149 `price` DECIMAL(10,2) NOT NULL COMMENT 'Price of the item',
150 `addedAt` DATETIME NOT NULL COMMENT 'When the price was added to history',
151 PRIMARY KEY (`id`)
152) COMMENT 'History of the product prices';
153
154CREATE TABLE `category` (
155 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
156 `isInstrument` BOOL NOT NULL DEFAULT 0 COMMENT 'This category will be displayed on main page when shopping by instrument',
157 `isFeatured` BOOL NOT NULL DEFAULT 0 COMMENT 'This category will be displayed on main page as main category',
158 `type` ENUM('P', 'S') NOT NULL DEFAULT 'P' COMMENT 'Indicates if the category is product or service',
159 PRIMARY KEY (`id`)
160) ENGINE=InnoDB;
161
162CREATE TABLE `address` (
163 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
164 `streetName` VARCHAR(100) NOT NULL COMMENT 'Name of the street',
165 `streetType` TINYINT NOT NULL COMMENT 'Type of the street. 1 Street, 2 Avenue ...',
166 `buildingNumber` VARCHAR(20) NULL COMMENT 'Number of the building on the street, e.g. 140, 1004A ...',
167 `additionalInfo` VARCHAR(100) NULL COMMENT 'Field to add aditional information about the address, e.g. AP 205 Bloco C ...',
168 `country` CHAR (2) NOT NULL DEFAULT 'BR',
169 `cityId` BINARY(16) NOT NULL COMMENT 'city FK',
170 `distritcId` BINARY(16) NOT NULL COMMENT 'district FK',
171 `postalArea` VARCHAR(20) NOT NULL COMMENT 'CEP, ZIP CODE, POSTCODE ...',
172 PRIMARY KEY (`id`)
173) COMMENT 'Address';
174
175CREATE TABLE `city` (
176 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
177 `name` VARCHAR(40) NOT NULL,
178 `distritcId` BINARY(16) NOT NULL COMMENT 'district FK',
179 PRIMARY KEY (`id`)
180) COMMENT 'City, Town ...';
181
182CREATE TABLE `district` (
183 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
184 `name` VARCHAR(40) NOT NULL,
185 `country` CHAR (2) NOT NULL DEFAULT 'BR',
186 PRIMARY KEY (`id`)
187) COMMENT 'City, Town ...';
188
189CREATE TABLE `review` (
190 `id` BINARY(16) NOT NULL COMMENT 'UUID PK',
191 `message` VARCHAR(255) NOT NULL COMMENT 'message',
192 PRIMARY KEY (`id`)
193) COMMENT 'Reviews for stores and items';
194
195