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

查看:49
本文介绍了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 数据库规范化、关系和外键的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆