1

I am creating a banking database and I have the following problem here.

Suppose CustomerA has an Account Number 4444 and through this I access all details related to the the customer I have a separate primary key but I generally query on this Account Number.

Now, for some reason the Account Number of CustomerA is changed from 4444 to 5555 and a new CustomerB is given the Account Number 4444.

I want my database to handle such a change, what approach should I apply?

Approaches I developed are: assigning a timestamp with the account number, this will help me to query efficiently to find out the current Account Number related Customer Name.

But I cannot design a query in which:

  • When I access CustomerB using 4444 only the current details related to B appear and not the one's 4444 is pointing for CustomerA, and
  • CustomerA when being accessed by 5555 shows all details including the one's which are being stored in the database by 4444
halfer
  • 19,824
  • 17
  • 99
  • 186
matta118
  • 17
  • 4
  • The most straightforward solution is to not use fields which change values as foreign/primary keys. In this case this means only use the account number to find the account id, and then use that as an identifier. – Vatev Jul 17 '15 at 06:57
  • Okay but i cant do that now i am already accessing everything through account number – matta118 Jul 17 '15 at 08:42
  • Well if it is not possible to find what you are looking for just by an account number and you only have an account number... – Vatev Jul 17 '15 at 08:46
  • Account numbers should not change – Ed Heal Jul 18 '15 at 14:41
  • The number of a customer's account could change. Of course it's true that it isn't a *number* that changes; the assignment of a number to a particular account is what changes. – nvogel Jul 18 '15 at 18:10

1 Answers1

0

I'm going to assume this is an educational exercise rather than a real banking application.

It doesn't really make sense to identify customer information with an account number alone. Customers can have more than one account and accounts can be held jointly by several customers, so an account number is unlikely to be a suitable key for customers. I suggest you create a customer number for that purpose.

I seriously doubt that any bank would be imprudent enough to allocate old account numbers to new customers. Just possibly that might happen after many years have elapsed since an account number was last used but it's probably not something you need to anticipate in your database design. Similarly, I would not expect to delete or overwrite customer account numbers because account details, including account numbers, are normally kept for years (probably decades).

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Thanks alot for taking time to look into this question , actually i want to create an upper view you can say my application is already in working state and now i have to add an option to change the account number which is used is various queries to get the customer details , i want to do a mapping such that account number + virtual_id becomes by secondary key or unique key you can say, and from that i map all accounts to virtual_id at the start and then change account numbers but need to adjust timestamp here also so that i can get only the latest account holder details – matta118 Jul 20 '15 at 09:02