MySQL将表实现到数据库中 [英] MySQL implementing tables into database

问题描述

我必须执行以下任务：
任务：
当前，数据库知道两种消息：

• 用户发布的消息，公开给所有人和所有人阅读


• 用户发布的消息，并且公开不公开。这些消息只能由发布用户标记为朋友的用户读取。

在此步骤中，您应添加第三种类型的消息。第三种类型的消息应仅由指定的收件人可读。
这意味着数据库需要提供以下内容：

• 一种区分三种消息类型的方法。这涉及到对邮件表的更改。


• 一种指定特定邮件的收件人是谁的方法。这可能需要一个附加表。

所有这些必须再次以最小的存储量实现，即，必须选择适当的存储空间。 MySQL手册中的数据类型。您可以假设随着时间的推移添加的消息总数可能达到1,000,000,000。
您的工作是为此目的执行必要的更改和附加表，以及所需的任何键和外键关系。

以下是我的两个表：User

 如果用户不存在则创建表（
ʻuser_id` int（10）unsigned NOT NULL auto_increment，
`given_name` varchar（60）默认为NULL，
`surname` varchar（60）默认为NULL，
`address` varchar（255）默认为NULL，
`city_id` int（ 10）unsigned NOT NULL，
`date_of_birth`日期时间默认为NULL，
`email`varchar（80）默认为NULL，
PRIMARY KEY（ʻ​​user_id`），
KEY`ix_user_surname `（姓氏），
键ix_user_given_name`（given_name），
键ix_user_name`（given_name`，姓），
键ix_user_date_of_birth`（`date_of_birth ），
键ix_user_email`（电子邮件），
键ix_user_city_id（city_id）
）ENGINE = InnoDB 
  

第二张表：Message

 如果不存在则创建表消息（
`message_id` int（10）unsigned NOT NULL auto_increment，
`owner_id` int（10）unsigned default NULL，
`subject` varchar（255）default NULL，
正文文本，
发布日期时间默认为NULL，
 is_public` tinyint（4）默认为0，
主键（message_id），
键ix_message_owner_id`（`owner_id`）
）ENGINE = InnoDB 
  

我的解决方案：我当时在想创建一个名为 Message_level的新表并具有 message_level_id列（将1,2,3表示为1 = public，2 = private，3 = specific）的说明。 message_level（在级别旁边注明公开，私有和具体）。然后，我可以将 Message_level用作 Message表中的外键，并将 is_public列替换为 message_level_id。

对不对？

我将如何处理指定特定邮件的接收者是谁的第二项任务？

p>

解决方案

我会这样：

 用户：user_id，给定名称，... 
 
消息：message_id，owner_id（fk用户），主题，正文，已发布，message_type_id（fk Message_type）... 
 
 Message_recipients：user_id（fk用户），message_id（fk消息）
 
Message_type：message_type_id，说明（1：公开，2：朋友，3：特定收件人）
  

I have to implement the tasks below: Task: At present, the database knows two types of messages:

• Messages that a user posts and that are public for anyone and everyone to read
• Messages that a user posts and that are non-public. These messages can only be read by users that the posting user has marked as friends.

In this step, you should add a third type of message. This third type of message should be readable by specified recipients only. This means the database needs to provide the following:

• A way of distinguishing between the three types of messages. This involves a change to the Message table.
• A way of specifying who the recipients of a particular message are. This will probably require an additional table.

All this must again be achieved with minimal amount of storage, i.e., you must choose the appropriate data types from the MySQL manual. You may assume that the total number of messages that are added over time may reach 1,000,000,000. Your job is to implement the necessary changes and additional table for this purpose and any keys and foreign key relationships required.

Here are my two tables first : User

CREATE TABLE IF NOT EXISTS `User` (
`user_id` int(10) unsigned NOT NULL auto_increment,
`given_name` varchar(60) default NULL,
`surname` varchar(60) default NULL,
`address` varchar(255) default NULL,
`city_id` int(10) unsigned NOT NULL,
`date_of_birth` datetime default NULL,
`email` varchar(80) default NULL,
PRIMARY KEY (`user_id`),
KEY `ix_user_surname` (`surname`),
KEY `ix_user_given_name` (`given_name`),
KEY `ix_user_name` (`given_name`,`surname`),
KEY `ix_user_date_of_birth` (`date_of_birth`),
KEY `ix_user_email` (`email`),
KEY `ix_user_city_id` (`city_id`)
) ENGINE=InnoDB

2nd table :Message

CREATE TABLE IF NOT EXISTS `Message` (
`message_id` int(10) unsigned NOT NULL auto_increment,
`owner_id` int(10) unsigned default NULL,
`subject` varchar(255) default NULL,
`body` text,
`posted` datetime default NULL,
`is_public` tinyint(4) default '0',
PRIMARY KEY (`message_id`),
KEY `ix_message_owner_id` (`owner_id`)
) ENGINE=InnoDB

MY SOLUTION: I was thinking of creating a new table called 'Message_level' and have columns 'message_level_id'(will refer to 1,2,3 as 1=public, 2=private, 3=specific) & 'message_level'(where it would state public,private and specific next to level). Then I can use the 'Message_level' as a foreign key into the 'Message' table and replace the 'is_public' column with 'message_level_id'.

Is my approach to this question right? is there another way I can do this to make it more efficient?

and how would I approach the second task of specifying who the recipients of a particular message are?

解决方案

I would go like this:

User: user_id, given_name, ...

Message: message_id, owner_id (fk User), subject, body, posted, message_type_id (fk Message_type)...

Message_recipients: user_id (fk User), message_id (fk Message)

Message_type: message_type_id, description (1:public, 2:friends, 3:specific_recipients)

这篇关于MySQL将表实现到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！