Format a value from other values in the table I have identified a location for data integrity problems in a design for a database I'm planning on building. I have lots of two way connections between maps in a game. These connections cause from 2 to 8 duplicate strings per connection. Due to this I have decided to add a names table.

As an example we have two locations Beach and Town, to move between them you need to move into the "Beach to Town" or "Town to Beach" entity.
As I'm defining regions with a tree / graph like structure, there would be five regions in total. The four described earlier. And "Beach - Town Path" which is the parent to both "Beach to Town" and "Town to Beach".

In a table this would look like:

ID  NAME
1   Beach
2   Town
3   Beach to Town
4   Town to Beach
5   Beach - Town Path


I can solve this outside of SQL easily with Python. However I would prefer this logic to be inside the database.

import string

formatter = string.Formatter()

names = {
    1: "Beach",
    2: "Town",
    3: "{names[1]} to {names[2]}",
    4: "{names[2]} to {names[1]}",
    5: "{names[1]} - {names[2]} Path",
}


def get_name(id: int) -&gt; str:
    name = names[id]
    while True:
        segments = list(formatter.parse(name))
        if len(segments) == 1 and segments[0][1] is None:
            break
        name = name.format(names=names)
    return name


print(get_name(1))  # Beach
print(get_name(2))  # Town
print(get_name(3))  # Beach to Town
print(get_name(4))  # Town to Beach
print(get_name(5))  # Beach - Town Path


How this works:


I have the table imported into Python. (I have used a literal for brevity)
We get the name from the table.

name = names[3]
# name = "{names[1]} to {names[2]}"

We check if the name has segments to be formatted.

segments = list(formatter.parse(name))
# segments = [('', 'names[1]', '', None), (' to ', 'names[2]', '', None)]
if len(segments) == 1 and segments[0][1] is None:
# if False:

We format the name with the linked names.

name = name.format(names=names)
# name = Beach to Town

We check if the name has segments to be formatted. Since it has we break out of the loop.

segments = list(formatter.parse(name))
# segments = [('Beach to Town', None, None, None)]
if len(segments) == 1 and segments[0][1] is None:
# if True:
    break

Return the desired name.

return name
# return "Beach to Town"



Back to SQL. This is an example table containing more examples:

ID NAME
1  foo
2  bar
3  {names[1]} - {names[2]}
4  {names[1]} - 
5  {names[4]}{names[2]}


Whilst I can just use the exact code above and get the desired output.
Is it possible to use SQL with something like the following to get the desired output?

SELECT name FROM names WHERE id = 1 -- "foo"
SELECT name FROM names WHERE id = 2 -- "bar"
SELECT name FROM names WHERE id = 3 -- "foo - bar"
SELECT name FROM names WHERE id = 4 -- "foo - "
SELECT name FROM names WHERE id = 5 -- "foo - bar"


There are no limit on how the data is stored or retrieved, except that duplicate names are not allowed and the parsing must happen in SQL.
While clarifying your problem you say "how can I make it so if I ever rename Beach I don't have hundreds of potential problems?" This sounds like a normalization problem to me. Looking at the examples above I think you're mixing data and formatting inside the database, then trying to untangle that in processing.

Starting from an analysis of the problem domain it looks like you have two entity types - Location (or names) and Path, where a Path connects exactly two Locations. This I would implement as two tables. As you're concerned about changing natural keys without touching many rows I'd implement surrogate primary keys (using pseudo code since each DBMS's dialect will vary).

Location
  LocationId    int primary key
  LocationName  char(999) not null

Path
  FromLocationId int not null FK references Location.LocationId
  ToLocationId   int not null FK references Location.LocationId

  Primary key (FromLocationId, ToLocationId)

insert Location values (1, 'Beach');
insert Location values (2, 'Town');
insert Path values (1, 2);


The presentation can be taken care of in SQL if you really want to, though I'd suggest letting the persistence classes of the application take care of it:

select 1 as ID, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.FromLocationId

union all

select 2, l.LocationName
from Path p
inner join Location l
  on l.LocationId = p.ToLocationId

union all

select 3, f.LocationName + ' to ' + t.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 4, t.LocationName + ' to ' + f.LocationName
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId

union all

select 5, f.LocationName + ' to ' + t.LocationName + ' Path'
from Path p
inner join Location f
  on f.LocationId = p.FromLocationId
inner join Location t
  on t.LocationId = p.FromLocationId;


Because of the UNION ALL each path will produce five rows of output. These are distinguished by the constant-valued ID column in each sub-query so a where ID = .. query will work as you imagine.

Likely you will have more than one path in your database. It may be worth adding the column PathId to Path. Then your application queries become where PathID = xxx and ID = 5.

Now if you rename "Beach" to "Banana" exactly one column of one row changes (just like 3NF says it should) but all results return the new name, guaranteed, with no possibility of inconsistent results.