数据库架构 - 基于“项目”创建私人消息传递

Question

users table:

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(23) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `unique_id` (`unique_id`) USING BTREE,
  UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8;

items table:

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_photo` text,
  `item_name` varchar(100) DEFAULT NULL,
  `item_description` varchar(500) DEFAULT NULL,
  `item_price` varchar(10) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_id` (`user_id`) USING BTREE,
  KEY `fk_category_id` (`category_id`) USING BTREE,
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `items_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8;

What I would wanna do is to create a private messaging system for my project. Before this I have implemented a Comment system and it works well (pull out comments which has the same item_id). You can see the DDL and query here. But when it come to this, I find it hard to think about private messaging model.

Basically, the private message is to bid the item price between TWO users only (the seller and the bidder). Other registered user cannot see others bidding.

Here's my try at creating Bids table:

CREATE TABLE `bids` (
  `id` int(11) NOT NULL,
  `bid` float DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `from_uid` varchar(255) DEFAULT NULL,
  `to_uid` varchar(255) DEFAULT NULL,
  `to_iid` int(11) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have also tried to make Foreign Keys into bids table, but it seems like it's already too complicated for me. So an error 1215 - Cannot add foreign key constraint came out :(

If anything just let me know.

---

EDIT: charset to utf8. Failed to create Foreign key constraints

Answer 1

MESSAGES

message_id
conversation_id
user_id
message

CONVERSATIONS

conversation_id
item_id
user_id

BIDS

bid_id
item_id
user_id
amount