Is it good practice to refer to a table with different names in a database schema?

Question

We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for instance).

We also used to have this thing concept in the code at some point, but then it was decided for various reasons to rename it (let's say to stuff), which was done over time. Now, the code no longer mentions thing except in SQL queries.

We unfortunately can't easily rename the table or columns since they're being heavily used, and we were wondering how to deal with new tables referring to this concept of thing/stuff.

• Some of the team think that the schema should be the most up-to-date as possible, and that we should use the new naming (stuff) everywhere new (in join table names and FK names referring to thing)
• Others think that the database schema should always stay consistent, and thus we should keep using the old naming (thing) anywhere, and handle the renaming in the code itself

We're trying to understand what are best practices here and what is advised to do in this situation where the DB schema and the code naming diverge?

Answer 1

Well yes, having a single name for a concept is A Good Thing (tm). However, how do you get to that place?

First question: is this a problem? If the one team (front-end?) only ever refers to "stuff" and the db people know that "stuff" and "thing" are synonymous, can you live with this as part of the application folklore? Is it causing an actual problem.

One way to change, if your DBMS supports this, is to define views over "thing". These will be of the form

create view Stuff as
select thing_id as stuff_id from Thing;

This will work for reads. Your DBMS may or may not support writes through a view.

With this in place you can work your way through the DB changing name one at a time and changing the application-facing view(s) to match.

In an ideal world I would handle the name change as a special project. Search the code repo for all occurrences of "thing". Change code to reference "stuff" instead. Write migration scripts to alter DB objects accordingly. Deploy & run the automated test suite (I know, I'm hilarious). Done.

Answer 2

There are many examples of best practices for schema naming e.g. here and here

Keeping it simple and based on experience and opinion of course, when designing schemas, we want:

PrimaryKey: Auto-incremented value in the naming pattern <entity name(singular)>_ID e.g. thing_id or ThingID.

UniqueKey: Add a unique constraint on a unique key. In a strong entity, usually on a real-world reference (e.g. student_id). In associative tables, these are usually on combinations of foreign keys (e.g student_id, course_id).

Foreign Keys: These are references to auto-incremented values in their source tables and so are the same data type and name as the values they reference. So without looking at the whole schema, if I see ThingID in a different table not called Thing, I know it points to the primary key of the Thing table.

In your case, I would say you should:

1. First decide what the correct solution should be for future maintainability and support of the schema alone without any other considerations. Imagine you are a new team member and you look at this new design with its naming structure, would you be able to understand it?
2. Determine what is preventing the team from executing the correct solution e.g. no one is comfortable with the code base or you didn't write it or you don't feel that anyone on the team has the wherewithal to modify the code and not break the application.
3. If #2 is insurmountable, then consider

• a. Making the changes in #1 but add a layer like a view (add a "v_tablename") with the same old names and call the views instead of the tables from the code so the application works
• b. Making the changes in #1 but add a layer or interface in the code base which allows some mapping of new field classes to old existing field classes so the application works
• c. Isolate the schema and application in a separate parallel environment in which changes are made in both code and schema and verified thoroughly to ensure that the application works.

4. If you have zero access to the code base, then you just make the correct changes for the new tables - perhaps using a prefix-naming standard so you can identify old schema vs new schema.
5. If old schema has the correct naming, of course continue with it.

Overall, treating the codebase with undeserved trepidation can force inch-by-inch workarounds/accommodations and eventually, the team ends up with a design and system in both code and schema that no-one fully understands. This possible reality can add risk to your organization.

Answer 3

Not sure what database you are using ... But have you considered using SYNONYMS on those tables ?

That way, new applications can start using the "new" names (specified by the synonyms), while older ones and maintenance scripts keep on using the "old" names. You can then start migrating the "old" applications to use the new names, and when everyone is using the new names, rename the tables and remove the synonyms.

This will only work on table names. You also mention renaming columns? In that case, you can use views instead of simple synonyms. But the process remains the same: a gradual switchover of applications, then a final switchover of tables once everyone uses the the definitions.

Going back to your original question: "Is it good practice to refer to a table with different names in a database schema?" ... no, not really. It can create all sorts of confusions. It should only be a temporary situation.