Bernard Lambeau has written an interesting blog about logical data independence – the ability to change the logical schema without having to change the external schema.
As an organisation/business naturally changes – new features and/or enhancements will need to be added to the logical database schema. Logical data independence means that it is possible to make these changes without necessarily have to make changes to existing clients of the database.
In terms of a SQL DBMS – such as MS SQL – loosely speaking, this is the ability to change base tables without having to change any client application code.
Bernard’s blog gives a good real world example of where logical data independence can be of such importance. His example is based around a hospital appointments system. Originally it only required the current status of the appointment eg booked, patient in waiting room, attended, cancelled. Overtime a new requirement came about such that the logical schema was required to take into account/record temporal related issues surrounding each possible status in the actual entity life history of an appointment. For example, an individual appointment could go from booked, to cancelled. The new requirement meant that each of these would now need to be recorded. He outlines how these changes might be implemented – using a number of different options including views and/or triggers – whilst not affecting any external clients of the database.
The only thing that I would add to this – which is a point that I made in a previous blog – Stored procedures why use them? – is that if you are using a SQL dbms, such as MS SQL, then external clients should not have direct access to base tables. All access should be via sprocs and/or views. This makes it easier to decouple external applications from changes to internal base tables.
Many development teams are not aware of the advantages, such as agility to change, logical data independence can give their organisation. This, plus the fact that many teams do not use automated database unit testing, often means that the logical schema is unable to keep pace with changes in the organisation, due to the fear of ‘breaking’ existing clients of the database.
This can cause a number of data management issues. As the existing schema cannot be changed often a new data silo is created and the associated data integration issues then need to be managed. Or the existing schema is ‘bent’ to fit the new requirements – existing tables/columns are misused – bringing along the accompanying data quality issues.