What is the best practice for this scenario?

Question

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?

Rajanikant Hawaldar · Answer

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 Singh · Answer

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.

Nitin Sontakke · Answer

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!

What is the best practice for this scenario?

Answers (3)