Coherent data in multiple tables
With my limited knowledge of databases, I'm finding myself in a bit of a bind here...
Best way to describe the problem is by example, I'd think, so here goes:
Say I have a site with a table for user accounts. These accounts hold the info needed for the basic site. It also holds some generic information, such as a username, a password, an e-mail address, etc.
Now I want to introduce a message board to the site, and I decide that the best solution is to use a premade one, phpBB for instance, which has its own table for user accounts.
Now, here comes to problem... I'd like users who already have an account on the main site to also have an account on the message board. Obviously, whenever users changed their information, their password for instance, on one of the two accounts, it should also be changed for the other.
This could be done through code (PHP in my case), of course, simply by having multiple INSERTs and UPDATEs - I could even add in logic checks to make sure that all the data was updated correctly. Problem is, this code would quickly become rather messy (especially when I add the third table of user accounts... say, an alliance with another site or something, to share user accounts), and I have a gut feeling that it may not be necessary.
So, basically, what I want is a database function that automatically updates several tables whenever I update information in one of them. Some voice in the back of my head is whispering "relational databases", but having only worked with MySQL I know very little about those - I'm not even sure if they could do the job.
Could anyone shed some light on if that is the solution? Or if there's a simpler/better solution? And if so, where I can read up on it? Any help would be greatly appreciated.
Well, it looks like you are setting yourself up for a real mess, if you are not careful.
Yes, a relational database should be involved, but you need to think carefully about structure in general. Usually, it is not a good idea to duplicate data to different tables, even if you can apply logic constraints to make sure your data stays consistent. Better to find a way to have a central repository of users, and then find a way to make each application query that table. In most relational databases, views provide a good way to accomplish this. A view is simply a query that is saved as if it is another table. Thus, you could have a central table called "users", but a view called "phpBB_users", which contains only the users and columns needed for phpBB. If you name this view the same as phpBB's normal user table, then you don't need to change a line of phpBB code. However, most relational databases still don't allow for INSERTs or UPDATEs into views, only SELECTs. (there are ways around this with triggers, which are another database mechanism, but it will take some thinking)
But, this is just the tip of the iceberg. Without at least some foundation in relational databases, and relational data theory, It would take too long to explain all the possibilities and problems here, but it would require some use of views, triggers, foreign keys, and stored procedures or SQL functions.
The short answer is yes, it can be done, but MySQL won't do it for you anytime soon (maybe eventually it will be capable of all this). So, if your applications depend on MySQL specifically, you will need to code your own workaround. If MySQL is not a requirement, you should look into PostgreSQL as a database, and spend some time reading about exactly what a relational database is. (search for books by Codd, C.J. Date, Fabian Pascal, Hugh Darwen, etc... or any of the books and book reviews at http://techdocs.postgresql.org/)