1

I have a database where each entity has a unique ID, but recently all of the entity names were changed (not in the same way) to improve readability. This has made a lot of the summary reports look like the following:

ID     Name            Total for the year     
3011   Joesmith          3000
3011   Joe Smith         1000
5024   DBS               400
5024   Deborah Smith     150

What's the best way of going about either correcting the old entries to match the new ones or simply displaying the new name for any matching ID? At the end of the day we can get the summaries we're interested in by just using the ID's, but ultimately we want to interpret those by seeing our names.

We're looking at about 1000 or so IDs with different names and maybe 50,000 or so rows in total.

  • 2
    1) How do you know which entry is "newer"? (it's obvious in your example, but how do you know in the db?), 2) The "Total for the year" values are different for the same ID. What is the "correct" value for each ID? – Barry Carter Oct 31 '17 at 17:28
  • @BarryCarter, 2). It seems they change "Joesmith" to "Joe Smith" in some places, but not in all places. I suppose the "Total for the year" value should be 4000. 1). The task of detecting the direction of editing automatically looks interesting... – Stanislav Kralin Oct 31 '17 at 19:43
  • Probably should be migrated to SO or somewhere. Also, more examples of "readability improvements" are needed... – Stanislav Kralin Oct 31 '17 at 19:47
  • 1
    1) that's a good question. There is a third column with a transaction date and so you could hypothetically take any entry after a certain transaction date to determine newer. 2) The "total for the year" is the total under the ID. If you try to organize by name you get that ugly split. – nerb Oct 31 '17 at 21:08
  • Please edit your question so that it contains all necessary information (the additional texts from your comments). It probably helps if you also write what database the data is in and what tools and skills you have available. – Jan Doggen Nov 1 '17 at 9:28
1

It's a bit strange that the same unique ID can be associated with more than one name. The database schema must have a design problem. One solution might be to clean up your 50,000 transactions one day by standardizing the names associated with the IDs. Software like Open Refine can help you to normalize your data.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.