MySQL best approach for db normalising, relationsh

问题:

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

回答1:

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)

回答2:

option 1:
enter the adress data first, and use that id when createing the row in StudentDetails

option 2:
change the field StudentDetails.Address so its allow NULL valus, enter the StudentDetails, then the adress, and then update the StudentDetails.Address

回答3:

It's better to use foreign keys in number format So the Username should be the id of the StudentDetails table.

Can you put the SQL Query that you had tried to run?