Capturing name changes - solutions?

[txag08]

I'm working on a pet project to familiarize myself with developing in ServiceNow Studio. I like to go to see live theater and I'm building a database of theater I've seen. I've run into a bit of a problem, and though I've come up with a solution, I'm not entirely satisfied and am just curious to see how others might attack it.

The problem is that I have a table of actors and I have a table of theater buildings. Both actors and buildings can change names. I also have a table that collects all of the performances I've seen. When I am looking at a performance record, I want to see the name of that actor or building at the time of the performance, regardless of what it might have changed to later.

I've gotten around this by creating tables of "alternate names." For actors, I created a "Credited as" field and just fill that in with an appropriate name. The problem I have with this is that the Actor related list contains the "Credited as" column and it is mostly a column full of empty values, because of course, this is an outlier condition.

For the building issue, I included two fields on the performance table: one that's a reference to the theater table and one that's a String for the theater name. I set up a business rule to automatically fill out the string value with the name of the theater at the time of the performance. If the names of the two fields are the same, I hide the second field on the form. If they are different, the second field will be displayed.

Anyway, like I said, the solutions work, but just seem a little clunky to me and I was curious to see how someone else might approach this or if there may even be some helpful feature in ServiceNow that I've overlooked.

[Mark Manders]

It's a custom app on a PDI, I assume, so you won't run into issues with the number of tables: create a new table for name changes. Put a type on it (theater/actor) so you only need to have one table and keep track there. You will need to fill the name of the actor on the role they played from that new table, so you have the correct name, based on the timestamp. Because of this, you will need to create records on this new table with every creation of a theater/actor. That will set the start date as well (or you can use their birthday/day the started using their acting name as start date) and if it changes, there will be a new entry. Your actor record stays up to date (how he is called now), but your related table contains the changes to the name so those can be referenced on your visited performances. And when you click through, it will take you to their current record on which you can see the history on the related list.