I've designed a mysql database made of several independent/unrelated tables to use in my office. Different tables hold information about clients (names, addresses etc), visits to the office (dates, reason etc) or other information like comments (related to a specific visit). When a known client enters the office I can retrieve all the relevant data, insert the new visit data and possible comments about the visit. I need to store visits in a specific order (1st visit 1st, etc). Sometimes I forget to store a visit and I have to enter the data later. The order of the visits gets messed up and I manually (update commands) correct it by changing the visits' autoincrementing primary key number (which represents the order). This way I get the right order of visits but comments (related to the changed primary key) cannot be retrieved. Is there any way to make sure that changing the primary key would make the necessary changes to the rest of the database, so that everything would work as before the changes? Do relational 1:many or other implementations accomplish that? And how? Please note that I'm an amateur, self-taugth "database designer".
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
First, yes it is possible to make sure changing a column in one table gets updated in all related tables. The concept is defined as a Foreign Key. In MySQL, the tables must be using the InnoDB storage engine. Here is the link to the 5.5 documentation on implementing foreign keys.
The basic gist is your child table (the table that relies on a foreign table's column value) needs to have this done to it:
Secondly, I would highly suggest not basing your order of visits on the auto-increment value. Auto-increment columns should have no meaning to your data.
If you have a date column, you should be able to get the order based on that column + the visitor, even if you add it manually at a later date. This would remove the 'need' of updating the autoincrement value.