What is the best practice for this scenario?
Table1: Customer
Table2: Order
CustomerId was stored in Order Table as a foreign key. What if someone accidentally renames Customer-A to Customer-X in Customer Table?
What is the best practice for this scenario?
Table1: Customer
Table2: Order
CustomerId was stored in Order Table as a foreign key. What if someone accidentally renames Customer-A to Customer-X in Customer Table?
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Nitin SontakkePosted Sep 25, 2022, 6:32 AM
First nobody should be 'accidently' changing the customer table. If someone does, revert it back as soon as the error is detected.
Implement auditing if stricter control needs to be implemented, recording who changed it when (and if required, what the value before and after for each column that was modified.
If business requirement is to see all older orders with id 9 as A and after modification as X. You will need to implement a history table and a strategy that all latest names are in current table and all previous data is in history table with from date and to date columns.
Ultimately it all boils down to what business wants and how far you / IT can to go implement it considering cost and labour and time. Database THEORY suggests you should not have customer name column in Order table.
While it is absolute NO NO, nothing in world would stop you doing that though!
Rajanikant HawaldarPosted Sep 25, 2022, 4:30 AM
As per business needs, customer name and customer id should be saved in Order table once sales order. Also provide an option to change customer name at only customer table, customer name can be changed as need but customer id will retain same, it is wrong to change customer name at order table.
Sachin SinghPosted Sep 24, 2022, 12:14 PM
so what? Name can be changed but not the id, you are storing CustomerId inside order's table correct?
For DB id is the identifier not other columns.