MySQL 数据库规范化、关系和外键的最佳方法

MySQL best approach for db normalising, relationships and foreign keys(MySQL 数据库规范化、关系和外键的最佳方法)

问题描述

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

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  | xx@xxx.xxx  | 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

现在我在输入地址时遇到问题，当我尝试输入学生详细信息时，它不会接受，直到有地址字段，如何最好地解决这个问题?当我从 StudentDetails 中选择某些字段并从 Addresses 中选择某些字段时，地址不显示.

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.

我是一个 mysql 菜鸟，所以我想要一些指导，看看规范化和结构是否正确完成，或者可以做得更好，这里是 fiddle 我无法让它正常工作，在我添加外键的行上不断出错，即使架构的构建在我的机器上运行良好.

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.

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

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.

然后您可以创建查询.

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 数据库规范化、关系和外键的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！