sqlcheck/examples/auctionmark-ddl.sql at development · bitsoex/sqlcheck · GitHub

This repository was archived by the owner on Mar 31, 2026. It is now read-only.

History

372 lines (355 loc) · 16.2 KB

Raw

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

/***************************************************************************

* Brown University *

* Massachusetts Institute of Technology *

* Yale University *

* *

* Andy Pavlo (pavlo@cs.brown.edu) *

* http://www.cs.brown.edu/~pavlo/ *

* *

* Visawee Angkanawaraphan (visawee@cs.brown.edu) *

* http://www.cs.brown.edu/~visawee/ *

* *

* Permission is hereby granted, free of charge, to any person obtaining *

* a copy of this software and associated documentation files (the *

* "Software"), to deal in the Software without restriction, including *

* without limitation the rights to use, copy, modify, merge, publish, *

* distribute, sublicense, and/or sell copies of the Software, and to *

* permit persons to whom the Software is furnished to do so, subject to *

* the following conditions: *

* *

* The above copyright notice and this permission notice shall be *

* included in all copies or substantial portions of the Software. *

* *

* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, *

* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF *

* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. *

* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR *

* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, *

* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR *

* OTHER DEALINGS IN THE SOFTWARE. *

***************************************************************************/

-- ================================================================

-- CONFIG_PROFILE

-- ================================================================

CREATE TABLE CONFIG_PROFILE (

cfp_scale_factor FLOAT NOT NULL,

cfp_loader_start TIMESTAMP NOT NULL,

cfp_loader_stop TIMESTAMP NOT NULL,

cfp_user_item_histogram VARCHAR(12000) NOT NULL

);

-- ================================================================

-- REGION

-- Represents regions of users

-- r_id Region's ID

-- r_name Region's name

-- ================================================================

CREATE TABLE REGION (

r_id BIGINT NOT NULL,

r_name VARCHAR(32),

PRIMARY KEY (r_id)

);

-- ================================================================

-- USERACCT

-- Represents user accounts

-- u_id User ID

-- u_firstname User's first name

-- u_lastname User's last name

-- u_password User's password

-- u_email User's email

-- u_rating User's rating as a seller

-- u_balance User's balance

-- u_created User's create date

-- u_r_id User's region ID

-- ================================================================

CREATE TABLE USERACCT (

u_id BIGINT NOT NULL,

u_rating BIGINT NOT NULL,

u_balance FLOAT NOT NULL,

u_comments INTEGER DEFAULT 0,

u_r_id BIGINT NOT NULL REFERENCES REGION (r_id),

u_created TIMESTAMP,

u_updated TIMESTAMP,

u_sattr0 VARCHAR(64),

u_sattr1 VARCHAR(64),

u_sattr2 VARCHAR(64),

u_sattr3 VARCHAR(64),

u_sattr4 VARCHAR(64),

u_sattr5 VARCHAR(64),

u_sattr6 VARCHAR(64),

u_sattr7 VARCHAR(64),

u_iattr0 BIGINT DEFAULT NULL,

u_iattr1 BIGINT DEFAULT NULL,

u_iattr2 BIGINT DEFAULT NULL,

u_iattr3 BIGINT DEFAULT NULL,

u_iattr4 BIGINT DEFAULT NULL,

u_iattr5 BIGINT DEFAULT NULL,

u_iattr6 BIGINT DEFAULT NULL,

u_iattr7 BIGINT DEFAULT NULL,

PRIMARY KEY (u_id)

);

CREATE INDEX IDX_USERACCT_REGION ON USERACCT (u_id, u_r_id);

-- ================================================================

-- USERACCT_ATTRIBUTES

-- Represents user's attributes

-- ================================================================

CREATE TABLE USERACCT_ATTRIBUTES (

ua_id BIGINT NOT NULL,

ua_u_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

ua_name VARCHAR(32) NOT NULL,

ua_value VARCHAR(32) NOT NULL,

u_created TIMESTAMP,

PRIMARY KEY (ua_id, ua_u_id)

);

-- ================================================================

-- CATEGORY

-- Represents merchandises' categories. Category can be hierarchical aligned using c_parent_id.

-- c_id Category's ID

-- c_name Category's name

-- c_parent_id Parent category's ID

-- ================================================================

CREATE TABLE CATEGORY (

c_id BIGINT NOT NULL,

c_name VARCHAR(50),

c_parent_id BIGINT REFERENCES CATEGORY (c_id),

PRIMARY KEY (c_id)

);

CREATE INDEX IDX_CATEGORY_PARENT ON CATEGORY (c_parent_id);

-- ================================================================

-- GLOBAL_ATTRIBUTE_GROUP

-- Represents merchandises' global attribute groups (for example, brand, material, feature etc.).

-- gag_id Global attribute group's ID

-- gag_c_id Associated Category's ID

-- gag_name Global attribute group's name

-- ================================================================

CREATE TABLE GLOBAL_ATTRIBUTE_GROUP (

gag_id BIGINT NOT NULL,

gag_c_id BIGINT NOT NULL REFERENCES CATEGORY (c_id),

gag_name VARCHAR(100) NOT NULL,

PRIMARY KEY (gag_id)

);

-- ================================================================

-- GLOBAL_ATTRIBUTE_VALUE

-- Represents merchandises' global attributes within each attribute

-- groups (for example, Rolex, Casio, Seiko within brand)

-- gav_id Global attribute value's ID

-- gav_gag_id Associated Global attribute group's ID

-- gav_name Global attribute value's name

-- ================================================================

CREATE TABLE GLOBAL_ATTRIBUTE_VALUE (

gav_id BIGINT NOT NULL,

gav_gag_id BIGINT NOT NULL REFERENCES GLOBAL_ATTRIBUTE_GROUP (gag_id),

gav_name VARCHAR(100) NOT NULL,

PRIMARY KEY (gav_id, gav_gag_id)

);

-- ================================================================

-- ITEM

-- Represents merchandises

-- i_id Item's ID

-- i_u_id Seller's ID

-- i_c_id Category's ID

-- i_name Item's name

-- i_description Item's description

-- i_initial_price Item's initial price

-- i_reserve_price Item's reserve price

-- i_buy_now Item's buy now price

-- i_nb_of_bids Item's number of bids

-- i_max_bid Item's max bid price

-- i_user_attributes Text field for attributes defined just for this item

-- i_start_date Item's bid start date

-- i_end_date Item's bid end date

-- i_status Items' status (0 = open, 1 = wait for purchase, 2 = close)

-- ================================================================

CREATE TABLE ITEM (

i_id BIGINT NOT NULL,

i_u_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

i_c_id BIGINT NOT NULL REFERENCES CATEGORY (c_id),

i_name VARCHAR(100),

i_description VARCHAR(1024),

i_user_attributes VARCHAR(255) DEFAULT NULL,

i_initial_price FLOAT NOT NULL,

i_current_price FLOAT NOT NULL,

i_num_bids BIGINT,

i_num_images BIGINT,

i_num_global_attrs BIGINT,

i_num_comments BIGINT,

i_start_date TIMESTAMP,

i_end_date TIMESTAMP,

i_status INT DEFAULT 0,

i_created TIMESTAMP,

i_updated TIMESTAMP,

i_iattr0 BIGINT DEFAULT NULL,

i_iattr1 BIGINT DEFAULT NULL,

i_iattr2 BIGINT DEFAULT NULL,

i_iattr3 BIGINT DEFAULT NULL,

i_iattr4 BIGINT DEFAULT NULL,

i_iattr5 BIGINT DEFAULT NULL,

i_iattr6 BIGINT DEFAULT NULL,

i_iattr7 BIGINT DEFAULT NULL,

PRIMARY KEY (i_id, i_u_id)

);

CREATE INDEX IDX_ITEM_SELLER ON ITEM (i_u_id);

-- ================================================================

-- ITEM_ATTRIBUTE

-- Represents mappings between attribute values and items

-- ia_id Item attribute's ID

-- ia_i_id Item's ID

-- ia_gav_id Global attribute value's ID

-- ================================================================

CREATE TABLE ITEM_ATTRIBUTE (

ia_id BIGINT NOT NULL,

ia_i_id BIGINT NOT NULL,

ia_u_id BIGINT NOT NULL,

ia_gav_id BIGINT NOT NULL,

ia_gag_id BIGINT NOT NULL,

ia_sattr0 VARCHAR(64) DEFAULT NULL,

FOREIGN KEY (ia_i_id, ia_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

FOREIGN KEY (ia_gav_id, ia_gag_id) REFERENCES GLOBAL_ATTRIBUTE_VALUE (gav_id, gav_gag_id),

PRIMARY KEY (ia_id, ia_i_id, ia_u_id)

);

-- ================================================================

-- ITEM_IMAGE

-- Represents images of items

-- ii_id Image's ID

-- ii_i_id Item's ID

-- ii_path Image's path

-- ================================================================

CREATE TABLE ITEM_IMAGE (

ii_id BIGINT NOT NULL,

ii_i_id BIGINT NOT NULL,

ii_u_id BIGINT NOT NULL,

ii_sattr0 VARCHAR(128) NOT NULL,

FOREIGN KEY (ii_i_id, ii_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

PRIMARY KEY (ii_id, ii_i_id, ii_u_id)

);

-- ================================================================

-- ITEM_COMMENT

-- Represents comments provided by buyers

-- ic_id Comment's ID

-- ic_i_id Item's ID

-- ic_u_id Buyer's ID

-- ic_date Comment's create date

-- ic_question Comment by buyer

-- ic_response Response from seller

-- ================================================================

CREATE TABLE ITEM_COMMENT (

ic_id BIGINT NOT NULL,

ic_i_id BIGINT NOT NULL,

ic_u_id BIGINT NOT NULL,

ic_buyer_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

ic_question VARCHAR(128) NOT NULL,

ic_response VARCHAR(128) DEFAULT NULL,

ic_created TIMESTAMP,

ic_updated TIMESTAMP,

FOREIGN KEY (ic_i_id, ic_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

PRIMARY KEY (ic_id, ic_i_id, ic_u_id)

);

-- CREATE INDEX IDX_ITEM_COMMENT ON ITEM_COMMENT (ic_i_id, ic_u_id);

-- ================================================================

-- ITEM_BID

-- Represents merchandises' bids

-- ib_id Bid's ID

-- ib_i_id Item's ID

-- ib_u_id Buyer's ID

-- ib_type Type of transaction (bid or buy_now)

-- ib_bid Bid's price

-- ib_max_bid ???

-- ib_date Bid's date

-- ================================================================

CREATE TABLE ITEM_BID (

ib_id BIGINT NOT NULL,

ib_i_id BIGINT NOT NULL,

ib_u_id BIGINT NOT NULL,

ib_buyer_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

ib_bid FLOAT NOT NULL,

ib_max_bid FLOAT NOT NULL,

ib_created TIMESTAMP,

ib_updated TIMESTAMP,

FOREIGN KEY (ib_i_id, ib_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

PRIMARY KEY (ib_id, ib_i_id, ib_u_id)

);

-- ================================================================

-- ITEM_MAX_BID

-- Cross-reference table to the current max bid for an auction

-- ================================================================

CREATE TABLE ITEM_MAX_BID (

imb_i_id BIGINT NOT NULL,

imb_u_id BIGINT NOT NULL,

imb_ib_id BIGINT NOT NULL,

imb_ib_i_id BIGINT NOT NULL,

imb_ib_u_id BIGINT NOT NULL,

imb_created TIMESTAMP,

imb_updated TIMESTAMP,

FOREIGN KEY (imb_i_id, imb_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

FOREIGN KEY (imb_ib_id, imb_ib_i_id, imb_ib_u_id) REFERENCES ITEM_BID (ib_id, ib_i_id, ib_u_id) ON DELETE CASCADE,

PRIMARY KEY (imb_i_id, imb_u_id)

);

-- ================================================================

-- ITEM_PURCHASE

-- Represents purchase transaction (buy_now bid or win bid)

-- ip_id Purchase's ID

-- ip_ib_id Bid's ID

-- ip_date Purchase's date

-- ================================================================

CREATE TABLE ITEM_PURCHASE (

ip_id BIGINT NOT NULL,

ip_ib_id BIGINT NOT NULL,

ip_ib_i_id BIGINT NOT NULL,

ip_ib_u_id BIGINT NOT NULL,

ip_date TIMESTAMP,

FOREIGN KEY (ip_ib_id, ip_ib_i_id, ip_ib_u_id) REFERENCES ITEM_BID (ib_id, ib_i_id, ib_u_id) ON DELETE CASCADE,

PRIMARY KEY (ip_id, ip_ib_id, ip_ib_i_id, ip_ib_u_id)

);

-- ================================================================

-- USERACCT_FEEDBACK

-- Represents feedbacks between buyers and sellers for a transaction

-- uf_id Feedback's ID

-- uf_u_id The user receiving the feedback

-- uf_i_id Item's ID

-- uf_i_u_id Item's seller id

-- uf_from_id The other user writing the feedback

-- uf_date Feedback's create date

-- uf_comment Feedback by other user

-- ================================================================

CREATE TABLE USERACCT_FEEDBACK (

uf_u_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

uf_i_id BIGINT NOT NULL,

uf_i_u_id BIGINT NOT NULL,

uf_from_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

uf_rating TINYINT NOT NULL,

uf_date TIMESTAMP,

uf_sattr0 VARCHAR(80) NOT NULL,

FOREIGN KEY (uf_i_id, uf_i_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

PRIMARY KEY (uf_u_id, uf_i_id, uf_i_u_id, uf_from_id),

CHECK (uf_u_id <> uf_from_id)

);

-- ================================================================

-- USERACCT_ITEM

-- The items that a user has recently purchased

-- ================================================================

CREATE TABLE USERACCT_ITEM (

ui_u_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

ui_i_id BIGINT NOT NULL,

ui_i_u_id BIGINT NOT NULL,

ui_ip_id BIGINT,

ui_ip_ib_id BIGINT,

ui_ip_ib_i_id BIGINT,

ui_ip_ib_u_id BIGINT,

ui_created TIMESTAMP,

FOREIGN KEY (ui_i_id, ui_i_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

FOREIGN KEY (ui_ip_id, ui_ip_ib_id, ui_ip_ib_i_id, ui_ip_ib_u_id) REFERENCES ITEM_PURCHASE (ip_id, ip_ib_id, ip_ib_i_id, ip_ib_u_id) ON DELETE CASCADE,

PRIMARY KEY (ui_u_id, ui_i_id, ui_i_u_id)

);

-- CREATE INDEX IDX_USERACCT_ITEM_ID ON USERACCT_ITEM (ui_i_id);

-- ================================================================

-- USERACCT_WATCH

-- The items that a user is watching

-- ================================================================

CREATE TABLE USERACCT_WATCH (

uw_u_id BIGINT NOT NULL REFERENCES USERACCT (u_id),

uw_i_id BIGINT NOT NULL,

uw_i_u_id BIGINT NOT NULL,

uw_created TIMESTAMP,

FOREIGN KEY (uw_i_id, uw_i_u_id) REFERENCES ITEM (i_id, i_u_id) ON DELETE CASCADE,

PRIMARY KEY (uw_u_id, uw_i_id, uw_i_u_id)

);

sqlcheck/examples/auctionmark-ddl.sql at development · bitsoex/sqlcheck · GitHub

Expand file tree

Breadcrumbs

auctionmark-ddl.sql

Latest commit

History

Breadcrumbs

auctionmark-ddl.sql

File metadata and controls