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".
Mysql – related column contents
MySQLrelational-theory
Related Question
- MySQL – eCommerce Orders Table: Save Prices or Use Audit/History Table?
- MySQL – How to Track Entity Updates (By Who and When)
- MySQL Optimization – Speeding Up 400GB MyISAM Table
- Mysql – thesql workbench migration changed the database. Can I trust it
- Mysql – Now to normalize table when position in group uniquely identifies entity
- MySQL – Ordering Table by Primary Key
Best Answer
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:
Where
foo_idexists in both the child tablebarand the parent tablefoo. Thefoo_idcolumn must be defined exactly the same in both tables (same size, type and unsigned/signed status).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.