It sounds like you should also look up some information on database indexes. just like r937 says, you should have an index whenever you are going to use that column in WHERE statements. In a nutshell, an index is like an index in a book. It organizes the values in a column (or columns), and tells you where to find it in the table. In this way, MySQL can find things faster. This is especially useful in JOINS because if you are joining two tables with 1000 rows (and that's not that big), checking if each row matches some criteria, that's 1000*1000 checks or 1 million. For bigger tables, a "full scan" like that takes forever. An index allows you to test much faster if the rows match, so it speeds things up (think factors of 1000). The down side is that each time a row is added to a table, the index has to be rebuilt to include it, but if you are doing any JOINs, it's worth it.
I've not had much time recently to take a look at this, but finally sat down and read up more on foreign keys, if my understanding is correct the following should work with the code I posted originally. (updated SQL Script)
Using the first three user tables as an example.
If I update a user's ID it will reflect in the other tables?
If I delete a user from the global_users table it will be removed from the other tables?
If there were multiple entries in the global_users_emails table for the same user, they would all be removed if the user was deleted from the global_users?
Thanks for your reply, I think I understand it now.
That's exactly what I've done. I've read the tutorial you linked me, although could you give me an example where SET NULL or RESTRICT would be used? I've also heard the there's one called DEFAULT, but heard this does not actually work at present, is that true?
NO ACTION is the default, but it's just a synonym for RESTRICT.
What I typically use is:
ON DELETE RESTRICT ON UPDATE CASCADE
This means that when you update the parent, the child gets updated too (ON UPDATE CASCADE).
The ON DELETE RESTRICT means if you try to delete a parent with children, it will actually throw an error. In order to delete a parent with children, you have to delete the children first, then it will let you delete the parent (now that it has no children). This will stop you from inadvertently deleting things without knowing every row that gets deleted.
ON UPDATE SET NULL means that if the parent is modified the child column actually gets set to NULL (I can't think of a good use case for this, but I'm sure there is one).
ON DELETE SET NULL means that if the parent is deleted, the child row stays but the column value is set to null. You don't delete the child data, but it allows you to delete the parent.