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)
相关文章