数据库正常化，关系和外键的MySQL最好的办法 [英] MySQL best approach for db normalising, relationships and foreign keys

问题描述

首先有一个用户名/密码验证步骤，然后数据库具有以下结构：

$ p $ ^是主键

*使用外键


1.StudentDetails表
====================== ================================================== ===
ID ^ |用户名|密码|电子邮件|地址* |网站|评论
==== + ============ + ========== + ============= + =========== + ========= + ==========
1 | xxxxxxxxxx | xxxxxxx | [e-mail] | 1 | http：// |文本

2.提交表
================================= ================================================== ========
ID ^ | Username * | SubmitDate | SelectedCourse * | Price * | Promotion * | SubmitComments | SubmitStatus *
=== + ========= + ========== + ======= + ====== + ========== + ==== ========== + =================
1 | xxxxxxxxx | 2013-7-12 | int | int | int |文字| int


3.SubmitComplete table
============================= =====================
ID ^ |用户名* | SelectionDate | SubmitStatus *
==== + ============ + =============== + ========== ======
1 | xxxxxxxxxx | 2013-08-01 | int


现在我遇到了输入地址的问题，当我尝试输入学生详细信息直到有地址字段才会接受，怎么解决呢？
当我做一个左连接从StudentDetails和地址的某些字段选择某些领域，地址不显示。

我是一个mysql noob，所以我' d喜欢一些指导，看看正常化和结构是否正确，或者可以做得更好，这里是小提琴我无法让它正常工作，不断增加的错误，我添加的外键，即使架构的建设在我的机器上运行良好。

小提琴控制台在第2行上说错误，但在我看来它实际上在第76行。
如果有什么我不清楚的，请告诉我。
谢谢

解决方案

好，让我解释一下它是怎么回事。我以两个表格为例，你可以在下面看到。

=简单模型>

然后您可以创建您的查询。

  MySQL的>显示表格; 
 + ---------------- + 
 | Tables_in_test | 
 + ---------------- + 
 |地址| 
 |学生| 
 + ---------------- + 
 2行（0.00秒）
 
 mysql>从学生中选择* 
 + ---- + ---------- + ----------- + 
 | id |名字| last_name | 
 + ---- + ---------- + ----------- + 
 | 1 | jhon |史密斯| 
 | 2 | anderson | neo | 
 | 3 |三位一体|杰克逊| 
 + ---- + ---------- + ----------- + 
 3行（0.00秒）
 
 mysql>选择*从地址; 
 + ---- + ----------------- + --------- + 
 | id |地址|学生| 
 + ---- + ----------------- + --------- + 
 | 1 | Av 1 2nd Street | 1 | 
 | 2 | Av 3 4 Street | 2 | 
 | 3 | St 23 7 Av | 3 | 
 + ---- + ----------------- + --------- + 
 3行（0.00秒） 
 
 mysql>选择s.name，s.last_name，a.address from students s join addresses a on a.student = s.id; 
 + ---------- + ----------- + ----------------- + 
 |名字| last_name |地址| 
 + ---------- + ----------- + ----------------- + 
 | jhon |史密斯| Av 1 2nd Street | 
 | anderson | neo | Av 3 4 Street | 
 |三位一体|杰克逊| St 23 7 Av | 
 + ---------- + ----------- + ----------------- + 
 3行（0.00秒）
  

There is an username/password verification step first then the database has following structure

^ is primary key

* uses foreign key


1.StudentDetails table
===========================================================================  
 ID^| Username   | Password | Email       | Address * | Website |Comments
====+============+==========+=============+===========+=========+========== 
1   | xxxxxxxxxx | xxxxxxx  | [e-mail]  | 1         | http:// | text

2.Submissions table
===========================================================================================
ID^|Username*|SubmitDate|SelectedCourse*|Price*|Promotion*|SubmitComments|SubmitStatus*
===+=========+==========+===============+======+==========+==============+=================
1  |xxxxxxxxx|2013-7-12 |  int          | int  |  int     | text         | int


3.SubmitComplete table
==================================================
ID^| Username * | SelectionDate | SubmitStatus *  
====+============+===============+================
1   | xxxxxxxxxx |  2013-08-01   | int

Now I'm having an issue entering the address, when i try to enter the student details it won't accept until there is an address field, how best to tackle that? When i do an left join selecting certain fields from StudentDetails and certain fields from Addresses, addresses don't show.

Im a mysql noob, so i'd like some guidance to see if the normalising and structure has been done correctly, or could it be done better, here is the fiddle i couldn't get it to work properly, kept getting errors on the lines where i added the foreign keys, even though the building of the schema worked well on my machine.

The fiddle console says error on line 2 but it looks to me it's actually on line 76. If there's anything i was unclear on, pls let me know. Thanks

解决方案

Ok let me explain you how it would be. I made an example with two tables that you can see below.

Then you can create your query.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| addresses      |
| students       |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from students;
+----+----------+-----------+
| id | name     | last_name |
+----+----------+-----------+
|  1 | jhon     | smith     |
|  2 | anderson | neo       |
|  3 | trinity  | jackson   |
+----+----------+-----------+
3 rows in set (0.00 sec)

mysql> select * from addresses;
+----+-----------------+---------+
| id | address         | student |
+----+-----------------+---------+
|  1 | Av 1 2nd Street |       1 |
|  2 | Av 3 4 Street   |       2 |
|  3 | St 23 7 Av      |       3 |
+----+-----------------+---------+
3 rows in set (0.00 sec)

mysql> select s.name,s.last_name,a.address from students s join addresses a on a.student=s.id;
+----------+-----------+-----------------+
| name     | last_name | address         |
+----------+-----------+-----------------+
| jhon     | smith     | Av 1 2nd Street |
| anderson | neo       | Av 3 4 Street   |
| trinity  | jackson   | St 23 7 Av      |
+----------+-----------+-----------------+
3 rows in set (0.00 sec)

这篇关于数据库正常化，关系和外键的MySQL最好的办法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！