1.order
CREATE TABLE `user_wallet_log` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`transaction_type` varchar(50) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`transaction_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_wallet_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.order_info
CREATE TABLE `order_info` (
`order_info_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`unit_price` decimal(10,2) NOT NULL,
PRIMARY KEY (`order_info_id`),
KEY `order_id` (`order_id`),
KEY `product_id` (`product_id`),
CONSTRAINT `order_info_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`order_id`) ON DELETE CASCADE,
CONSTRAINT `order_info_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.product
CREATE TABLE `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`product_name` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL DEFAULT '0.00',
`stock` int(11) NOT NULL DEFAULT '0',
`type_id` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`product_id`),
KEY `type_id` (`type_id`),
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `product_type` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4.product_type
CREATE TABLE `product_type` (
`type_id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(100) NOT NULL,
`parent_id` int(11) DEFAULT NULL,
`description` text,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.user
CREATE TABLE `user` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`),
UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
6.user_wallet
CREATE TABLE `user_wallet` (
`wallet_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`balance` decimal(10,2) NOT NULL DEFAULT '0.00',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`wallet_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_wallet_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7.user_wallet_log
CREATE TABLE `user_wallet_log` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`transaction_type` varchar(50) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`transaction_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_wallet_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;