If I share a Google Spreadsheet with someone, I will share a link like this:
https://docs.google.com/spreadsheets/d/1UL0Aii1aaErbUm6pdmZ_D9dsqx2p3BPZ5GHxz7TH37M/edit
This means that I am free to edit the name of the file (for example to "Book – absolute final version really7"), the Google Drive folder it is located in etc. etc. while the link will still work. Would it be possible to simulate something similar in a database? The problem I am trying to solve is the intense pain and risk I have to go through every time I need to rename a column, table, view or stored procedure in the database, or if I want to move it to a different schema. Because of this pain, I currently have all sorts of naming of all these things so you will see a primary key lookup looking like this in my database:
select thisIsAStoredProcedure(acolumn) from a_Table_Name where a_Table_Name_ID = 70;
Needless to say, it is impossible to get fluent in the naming of our database objects. Also, because we are very agile with releasing new updates (uptime is not much of a concern so fast is better than correct at first attempt), we keep adding more objects with wrong naming.
Postgres will help me out in some cases, but it will not fix references to a table inside a stored procedure and the same goes for any references from our any application, or configuration script.
What I am after is that if I write a stored procedure then the stored procedure will use the following while running:
select 1UL0Aii1aaErbU from aErbUm6pdm;
While what I see when I read the stored procedure in my IDE is this:
select a_column from a_table;
Another solution could be to add aliases, so after renaming a_table to b_table then querying a_table would still work, but all queries to the a_table would get logged and I could search for those queries that get run towards the old table. This would mean that there would be no risk involved in renaming a table. The same goes for columns, stored procedures etc. Does such a solution exist?
I realize that the first solution requires major work in the IDE but having such an IDE would mean that I could safely rename tables and columns at will.
Maybe a quick-fix solution to this would be a Mac/Windows hack where if any text editor presents the following text to me: 1UL0Aii1aaErbU then that will always get auto-replaced with a_column no matter where it is presented. I believe AutoHotKey.com could achieve this on Windows.
Best Answer
For tables and columns you could define views. The underlying objects can have whatever abstruse naming you choose. The views would make that human-friendly in the context it is used. When the design changes, create a new view with different names and you're off and running!
For programmables, such as functions and stored procedures, a wrapper presenting intelligible name could simply forward parameters to the underlying objects.
However, I do not think this is a great pattern. A relational table is more than a bucket of strongly typed variables. Columns also encode meaning. Once a column has been christened, say, iso_country_code, no other values should be permitted in that column just because they're two characters long. To repurpose columns makes a mockery of DRI. This is not how an RDBMS was designed to work.
Now, there are many fine dynamic schema persistence layers in existence. These were designed specifically to store items with variable semantics side-by-side. If this is your use-case, use one of these.