Project 1
( Above diagram get from internet due no system to me…) User details :
{ "cartId": 604638499041, "userAuthToken": "6^t@CDm6DY7FZZq3E!0lT3rxb02d7&", "registeredUser": true, "items": [ { "itemId": "1000000122", "quantity": 2, "group": [ "5e31a1f9fcc2b500089c10e8" ], "price": { "sale": 0, "base": 120, "discount": { "price": 0 }, "currency": "USD" }, "extra": {} } ]}
Product details:
db.productdetals.insertOne( {
item: "boat",
price: 9.99,
qty: 25,
size: { h: 14, l: 21, w: 1 },
features: "Beautiful, handmade journal.",
categories: ["audio", "bestseller"],
image: "items/product .jpg"
} )
Product review
CREATE TABLE `shop`.`product_review` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`productId` BIGINT NOT NULL,
`parentId` BIGINT NULL DEFAULT NULL,
`title` VARCHAR(100) NOT NULL,
`rating` SMALLINT(6) NOT NULL DEFAULT 0,
`published` TINYINT(1) NOT NULL DEFAULT 0,
`createdAt` DATETIME NOT NULL,
`publishedAt` DATETIME NULL DEFAULT NULL,
`content` TEXT NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_review_product` (`productId` ASC),
CONSTRAINT `fk_review_product`
FOREIGN KEY (`productId`)
REFERENCES `shop`.`product` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
ALTER TABLE `shop`.`product_review`
ADD INDEX `idx_review_parent` (`parentId` ASC);
ALTER TABLE `shop`.`product_review`
ADD CONSTRAINT `fk_review_parent`
FOREIGN KEY (`parentId`)
REFERENCES `shop`.`product_review` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Customer details
db.customers.insertOne( {
_id: "journalfanatic@e/mail.com",
fname: "Journal",
lname: "Fanatic",
hashedAndSaltedPassword: "$1$O3JMY.Tw$AdLnLjQ/5jXF9.MTp3gHv/",
emailVerified: false,
address: {
country: "United States",
street1: "99 Main Street",
street2: "Apt #3",
city: "Boston",
state: "MA",
zip: "74586"
}
} )
- Seller details
db.seller.insertOne( {
_id: "journfanatic@e/mail.com",
fname: "nal",
lname: "tic",
hashedAndSaltedPassword: "$1$O3JMY.Tw$AdLnLjQ/5jXF9.MTp3gHv/",
emailVerified: false,
address: {
country: "United States",
street1: "99 Main Street",
street2: "Apt #3",
city: "Boston",
state: "MA",
zip: "74586"
}
} )
Order details
orders` ( `orderID` int(10) unsigned NOT NULL auto_increment, `agentID` int(10) unsigned NOT NULL, `order_date` datetime NOT NULL, `senderID` int(10) unsigned NOT NULL, `receiverID` int(10) unsigned NOT NULL, PRIMARY KEY (`orderID`)) `order_details` ( `order_details_id` int(10) unsigned NOT NULL auto_increment, `orderID` int(10) unsigned NOT NULL, `packageID` int(10) unsigned NOT NULL, PRIMARY KEY (`order_details_id`), KEY `fk_orderID` (`orderID`)) `order_status` ( `status_id` int(10) unsigned NOT NULL auto_increment, `order_details_id` int(10) unsigned NOT NULL, `descrID` int(10) unsigned NOT NULL, `date_time` datetime NOT NULL, PRIMARY KEY (`status_id`), KEY `theDate` (`date_time`), KEY `stat` (`descrID`,`order_details_id`,`date_time`)) `order_statuses` ( `descrID` int(10) unsigned NOT NULL auto_increment, `descr` varchar(255) collate utf8_bin NOT NULL, `public` smallint(6) NOT NULL COMMENT '1 or 0', `leg` tinyint(4) NOT NULL COMMENT 'carrier code', PRIMARY KEY (`statusID`))
It will give all the order
select odd.order_details_idfrom order_details odd, orders odwhere odd.orderID = od.orderID and ( select oas.statusID from order_status oas, order_statuses oss where oas.descrID = oss.descrID and oss.leg=1 and odd.order_details_id =oas.order_details_id order by oas.date_time desc limit 1)=2Order transaction CREATE TABLE `shop`.`transaction` (`id` BIGINT NOT NULL AUTO_INCREMENT,`userId` BIGINT NOT NULL,`orderId` BIGINT NOT NULL,`code` VARCHAR(100) NOT NULL,`type` SMALLINT(6) NOT NULL DEFAULT 0,`mode` SMALLINT(6) NOT NULL DEFAULT 0,`status` SMALLINT(6) NOT NULL DEFAULT 0,`createdAt` DATETIME NOT NULL,`updatedAt` DATETIME NULL DEFAULT NULL,`content` TEXT NULL DEFAULT NULL,PRIMARY KEY (`id`),INDEX `idx_transaction_user` (`userId` ASC),CONSTRAINT `fk_transaction_user`FOREIGN KEY (`userId`)REFERENCES `shop`.`user` (`id`)ON DELETE NO ACTIONON UPDATE NO ACTION); ALTER TABLE `shop`.`transaction` ADD INDEX `idx_transaction_order` (`orderId` ASC);ALTER TABLE `shop`.`transaction` ADD CONSTRAINT `fk_transaction_order`FOREIGN KEY (`orderId`)REFERENCES `shop`.`order` (`id`)ON DELETE NO ACTIONON UPDATE NO ACTION;