Jeffrey Williams wrote:
I missed the point about starting with a view from day one. If the only reason for using the view is to allow for 'future' refactoring - then I would not recommend that approach. If your system is being refactored that often - to the point where you have to add, remove or rename columns - there are probably much deeper issues that need to be addressed.
No, definitely not refactoring often 🙂
Code base is 20 years old, some design decisions back then now need serious revamping. But if we do not have "Day One Views on All Tables" then I can't take advantage of that for Refactoring "when the time comes" and have to do it some other way (all the ways I have used are "hard", but maybe I will learn from this thread a better way. I'll pose the question at the bottom.)
Changing column order isn't something that should be done either - always add new columns using ALTER TABLE ADD COLUMN. In fact, you cannot change column order without recreating the table - and if you do that then it would be much easier to create a new table, migrate the data and recreate the view to use the new table.
If I add a column that would be ADD COLUMN and not Re-Create Table (assuming no changes that require Re-Create Table). But ... column order IS important to us from a defensive programming perspective. The columns in our tables follow a convention. For example, all tables start with Create/Update Date/Person, and then the PKey. There are a number of columns that we "sometimes use", such as IsActive, which are always "in the same place". If we just bolt them in the middle / end it increases the chance that someone, doing maintenance of the code later or many-years-later, overlooks them.
If I have to add one "common columns" later then it goes on the end, using ADD COLUMN, but that table is flagged for Re-Create at the earliest opportunity. But ... if all my access to the Table was via a VIEW then the column sequence of my VIEW could be changed willy-nilly ... so perhaps I wouldn't bother with the Re-Create at all.
One of our 3rd party vendors has a whole load of "admin" columns in their tables - the usual Who/When, and also a Transaction No that ties all multi-table changes together, and some others. They added them to the end of their tables (fair enough) and since then "other columns have been added later". They added those Admin Columns to the FRONT of their Audit Tables so no easy way to just do a SELECT * UNION when I want a quick "data history". We work hard to avoid those sorts of snags with our tables because they lead to bugs at worst, and "increased cost of working" at the least.
Jeff Moden wrote:
If, for example, you change the order of columns in a table, the view will actually display the wrong data for the columns that follow the change (from left to right on a previously unchanged table). If they're all the same datatype, it will be a "silent failure" that wreak havoc on your results.
I've come across that, thanks for the reminder
It makes no sense to have view on tables that will never change and most will not change. It reminds me of building C.R.U.D. for reference tables. It makes no sense even under the claim of "future proofing".
The CRUD tables are a good point ... but (although very rare) even there I have split one-into-two, and combined two-into-one
Except for having a shedload of views cluttering up your database and having to maintain both a table and a view every time you need to make an otherwise trivial change to a table, there's no real harm, I suppose.
I did a quick stock-take
SELECT [Count]=COUNT(*), type_desc
GROUP BY type_desc
ORDER BY type_desc
Compared to TABLES
Views = 1x
Sprocs = 6x
If I did added "View for every Table" that would raise Views to 2x ... I think the "number of objects" would be tolerable (but that doesn't make it a good idea of course ...).
My recommendation is that having a view per table is serious overkill even when it comes to security advantages and you should use an "overlying" view only when necessary.
The code base is 20 years old, there are some tables which really really need refactoring but we are not getting around to it "because it is hard / time consuming / hard to justify" - i.e. "too costly", but not doing it is pretty costly too of course .... If someone made a table yesterday and told me today they needed to refactor it "we would be having a conversation about that" 🙂 but when the need arises, the way we do it currently makes it seem like "blooming hard work" - in terms of fixing up existing code.
Perhaps instead of my proposed solution I should ask the good folk here how do you handle refactoring a table?
I'll try to think of some decent examples, perhaps you have better examples of where you have had to refactor in the past
Old Status Column 1, 2, 3, 4, ... now needing to be split into two Status Columns (Status and Sub-Status, or Two different status codes for slightly different business purposes)
Used a Natural Key as PKey (and FKey) assuming that data would be static ... later decided I needed an IDENTITIY and move the Natural Key to become a "non PKey modifiable field"
Started off with ID and then we had a company merger / multiple office sites / whatever and now I need a GUID (maybe not to replace the ID as PKey but at the very least "additional to it" to allow MERGE of database)
Had a Column called "Notes" in loads of tables and then decided that users freely editing them, and rewriting history!!, was a bad idea, plus would like consistent Person/Date/Text formatting, so moved ALL the Notes from ALL tables to a central (JOINed) NOTES table (e.g. Table/Person/Date/TextItem)
My aim is to make any future "refactor" of the table easier - by having the view be able, at any future point, to provide both legacy and New Column names (and if necessary the Legacy columns will be via Computed values)