Improve bad naming of tables and fields on database tables without migrate your data

Currently I’m working on a project that will implement new features over a legacy PostgreSQL database.

This database that is connected to multiple systems that consume and insert data into it.

Since it’s a legacy database, several engineers have been working on that database over the years and they didn’t follow any naming conventions or best practices for naming the tables and fields, which is very common neglect.

I’m dealing with tables like this one

For us, It won’t be cheap to redesign the table names and fields (not talking about the tables relations design, just naming) since, as I said, there are several systems consuming those tables. 

As per experience, to improve it, we will need to create new tables, migrate the data into the new tables and migrate the related systems code to use the new naming, not a chance.

You might think, why is it a problem just the naming, just use it!. Ok, Fair enough, however, not following a convention and using special chars like ~, or ` (acute) on the fields will be a prompt for errors some day, I know that from experience.

Besides that, the project will be Implemented in Laravel, as you might know, Eloquent (the Laravel ORM) will take advantage of the database if the tables follow their naming convention, it will make it easier to work with in the long term.

So thinking and researching how to improve it came to my mind to use database views. 

In my terms, a database view is a database permanent query that is accessible like a data table. 

It will be created from a query and it’s stored into the database, however, the view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

Also, in PostgreSQL we have updatable views. From the PostgreSQL docs

Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

• The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
• The view’s select list must not contain any aggregates, window functions or set-returning functions.

With that info I know that 

• I can project my table into a view with a new name
• I can define the output fields with a new name
• I can access through the view to insert/delete/update the table

I think, for this case, a view can be my savior.

I want to use the view as a proxy for the real table, in this proxy I will follow naming conventions for the table and their fields.

How I did it?, I just created a view for the table using SQL.

And that’s it, now I have a database view called clients that will behave as a database table (for the developer usage interface, internally we know it will be another thing).

Laravel

Now, In Laravel I can just Make a model called Client and bind it to the view.

And it works, since the usage interface for the views are the same as for a table. For Eloquent the view is just another table.

If you want, you can also create a Database Migration to propagate these changes into the databases without the need to create the view manually, I have not found a syntax on Laravel to create a view, not sure if there is one, however, there is always the chance to run the raw DB statement.

Make a migration and implement something like this.

Downside

There is always a trade-off, keep in mind that the view needs to run every time that we access to the view, in the case, the access will be a full table scan

Pay attention to the cost of the View operation before to be sure that it will work in your case, in my case is affordable.

I hope this helps you if you have a similar issue.