470,632 Members | 1,328 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Programming

1
If multiple tables have the same fields, what is a good way to do synchronous updates? For example, the user name field of the order data table, the user mobile number field, needs to be updated as the field of the user table is updated
2 Weeks Ago #1
1 5956
zmbd
5,444 Expert Mod 4TB
Hello Livre
Welcome to Bytes...
You've provided precious little for us to work with, are the relationships setup between the tables on these fields, etc...
Take a look at MySQL UPDATE Statement
See if that's on the right path.... personally I've never had to update across multiple tables with a mysql database... (read on to see why)

However, from what you do provide, at first read, it sounds to me like your database is not normalized (see link)
> Database Normalization and Table Structures.
If this is the case, your very first step SHOULD be to start normalizing your database - it is a pain, it is well worth every second you put in to do so as a DBA because doing so will save weeks and months of time in developing views/queries and maintaining the overall data integrity.

Normalizing your data would make updates like what I think you asking about very simple; for example (please ignore spelling):
Expand|Select|Wrap|Line Numbers
  1. VERY Simple Example
  2. [tbl_employ]
  3. [PK_Customer][FamilyName][FirstName]
  4. [1          ][Doe       ][John        ]
  5. [2          ][Smith     ][Shellie     ]
  6.  
  7. [tbl_Telephone]
  8. [PK_Info][FK_Employ   ][Telephone]
  9. [1      ][1           ][555-555-0001]
  10. [2      ][1           ][555-555-0002]
  11. [3      ][1           ][555-555-0003]
  12. [4      ][2           ][555-555-9001]
  13. [5      ][2           ][555-555-9002]
  14. [6      ][2           ][555-555-9003]
"PK " indicates the primary key, this is unique to the record and ideally has nothing related to the record data
"FK " indicates a foreign key which points back to a record in a related table
The relationship is one-to-many between [tbl_employ] and [tbl_Telephone] on the fields
[tbl_employ]![PK_Customer] and [tbl_Telephone]![FK_Employ]
(if you don't understand this, please see the link above covering normalization)

Say we publish a report with each employ's name and telephone numbers and "Shellie" informs us that the first name is misspelled and should be "Shelly"!
Now with a normalized database we only need to go to [tbl_employ] and edit one record and reprint.
HOWEVER
Say that in [tbl_Telephone] we had added fields for [FamilyName] and [FirstName] then we would not only have to edit the record in [tbl_employ] but also three records in [tbl_Telephone]
2 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Martin | last post: by
12 posts views Thread by G. | last post: by
7 posts views Thread by Robert Seacord | last post: by
30 posts views Thread by Jakle | last post: by
47 posts views Thread by Thierry Chappuis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.