通过标准化工作(3NF)

Question

我计划这个网站已经有一段时间了，几周前开始把它放在一起，我得说事情进展得非常顺利，直到有一天我被介绍到规范化。我花了很长时间才掌握它是什么以及为什么需要它，但我认为我现在接近实现我的总体目标了。

所以这个场景是，我有一个网站，会员可以加入并建立他们自己的报纸/博客，并为它分配作者。该站点主要由members_table, authors_table, newspapers_table和posts_table组成，我已经经过并标准化得到:

+----+-----------+----------+-----------------+----------+----------+------------+------------+
| ID | FIRSTNAME | SURNAME  | EMAIL           | USERNAME | PASSWORD | AVATAR     | JOINED     |
+----+-----------+----------+-----------------+----------+----------+------------+------------+
| 01 | Brian     | Griffin  | [e-mail] | briang   | ***      | hdhs.jpg   | 2014-07-31 |
| 02 | Meg       | Griffin  | [e-mail]   | megg     | ***      |            | 2014-07-31 |
| 03 | Peter     | Griffin  | [e-mail] | peterg   | ***      | jaunsq.jpg | 2014-07-31 |
| 04 | Glen      | Quagmire | [e-mail]  | glen     | ***      |            | 2014-07-31 |
+----+-----------+----------+-----------------+----------+----------+------------+------------+

作者

+----+-----------+----------+-----------------+-------------------+---------+----------+---------+
| ID | FIRSTNAME | SURNAME  | EMAIL           | BIO ( REQUIRED )  | TWITTER | FACEBOOK | WEBSITE |
+----+-----------+----------+-----------------+-------------------+---------+----------+---------+
| 01 | Brian     | Griffin  | [e-mail] | About me...       | URL     |          | URL     |
| 02 | Meg       | Griffin  | [e-mail]   | About me...       | URL     |          |         |
| 03 | Peter     | Griffin  | [e-mail] | About me...       |         | URL      | URL     |
| 04 | Glen      | Quagmire | [e-mail]  | About me...       | URL     | URL      |         |
+----+-----------+----------+-----------------+-------------------+---------+----------+---------+

**社交应该在这里被打破吗?如果是，怎么做?

Newspaper_Categories

+----+-------------------+
| ID | CATEGORY          |
+----+-------------------+
| 01 | Lifestyle         |
| 02 | Auto Mobiles      |
| 03 | Entertainment     |
| 04 | Food & Drink      |
| 05 | Internet          |
+----+-------------------+

报纸

+----+-----------------------+----------+---------+------------------+------------------+
| ID | NAME                  | CATEGORY | AVATAR  | BIO ( REQUIRED ) | OWNER ( MEMBER ) |
+----+-----------------------+----------+---------+------------------+------------------+
| 01 | Spooner Street Weekly | 01       | 311.jpg | About Us...      | 01               |
| 02 | A Dogs Life           | 01       |         | About Us...      | 01               |
| 03 | In The Kitchen        | 04       | js.jpg  | About Us...      | 02               |
+----+-----------------------+----------+---------+------------------+------------------+

Owner应该在这里还是在一个名为Newspaper_Owners的表

,

+----+-------------------+
| ID | TYPE              |
+----+-------------------+
| 01 | Facebook          |
| 02 | Twitter           |
| 03 | Google            |
| 04 | Flickr            |
| 05 | Youtube           |
+----+-------------------+

Newspaper_Socials

+----------+--------+------+
| NEWSAPER | SOCIAL | LINK |
+----------+--------+------+
| 01       | 01     | URL  |
| 01       | 02     | URL  |
| 01       | 05     | URL  |
| 01       | 01     | URL  |
| 02       | 02     | URL  |
| 02       | 04     | URL  |
| 03       | 01     | URL  |
+----------+--------+------+

Post_Categories

+-------------------+--------------------+
| ID | NEWSPAPER_ID | CATEGORY           |
+----+--------------+--------------------+
| 01 | 01           | Glens Girls        |
| 02 | 01           | In The Clam        |
| 03 | 01           | Peters Shenanigans |
| 04 | 02           | Martini Recipes    |
| 05 | 03           | Housewife Tips     |
+----+--------------+--------------------+

文章

+----+----------+-------+---------+----------+------------+-------+
| ID | CATEGORY | TITLE | ARTICLE | FEATURED | ADDED      | VIEWS |
+----+----------+-------+---------+----------+------------+-------+
| 01 | 01       | Title | Article | 0        | 2014-07-31 | 200   |
| 02 | 01       | Title | Article | 0        | 2014-07-31 | 220   |
| 03 | 03       | Title | Article | 1        | 2014-07-31 | 232   |
| 04 | 05       | Title | Article | 0        | 2014-07-31 | 143   |
| 05 | 05       | Title | Article | 1        | 2014-07-31 | 311   |
+----+----------+-------+---------+----------+------------+-------+

Post_Photos

+---------+-----+--------------+------+
| POST_ID | ALT | PHOTOGRAPHER | LINK |
+---------+-----+--------------+------+
| 01      | Alt | John Smith   | URL  |
| 02      | Alt |              |      |
| 03      | Alt | Mike Jones   |      |
| 05      | Alt | Adam West    | URL  |
+---------+-----+--------------+------+

Post_Keywords

+---------+---------+
| POST_ID | KEYWORD |
+---------+---------+
| 01      | Keyword |
| 01      | Keyword |
| 01      | Keyword |
| 01      | Keyword |
| 02      | Keyword |
| 02      | Keyword |
| 03      | Keyword |
| 03      | Keyword |
| 03      | Keyword |
+---------+---------+

如果每个帖子只允许3个关键字，这些可以添加到Posts表的末尾吗?

Author_Posts

+-----------+---------+
| AUTHOR_ID | POST_ID |
+-----------+---------+
| 01        | 01      |
| 01        | 02      |
| 01        | 03      |
| 02        | 04      |
| 03        | 05      |
+-----------+---------+

有没有人能让我知道我是否在这个结构的正确行上，并给我一些关于Primary Keys和Foreign Keys ???应该去哪里的指针?

Answer 1

"**社交应该在这里被打破吗?如果是，怎么做?"

是的，你已经有社交桌了。添加一个新的多对多用户表