|
| Featured Contents |
| Question of the Day |
|
| The Voice of the DBA |
Muting the Immutable This editorial was originally published on Aug 26, 2015. It is being re-run as Steve is on holiday. The idea of 'static data', 'enumerations', reference data', 'seed data' or 'domain data' is rather alien to relational databases because it is unnecessary to invent such special categories. However, such data is easily accommodated, as are the many other types that the average application programmer will never come across. RDBMSs are naturally good at coping with data that is perceived as having special requirements, importance or significance. However, it is often raised as a problem when building and deploying databases. A lot of database developers, for example, hit the problem of having data that is, or should be, immutable. You wouldn't, for example, want to change ISO values for countries, but you've got to have them for the database to work. It is basically part of the version of the database. Normally, we just use a post-build script that inserts the correct data into a table, and then, for 'reference' data, give all database roles read-only access to the table. By doing this we ensure that the version of the data is tied to the version of the database and can't be changed. 'Seed' data is treated much the same way but with different user-access. However, this doesn't suit some build systems, such as SSDT. It makes the mistake of believing that a build should contain only DDL. It restricts the users to writing only DDL build scripts and spits out any DML it finds. It is an awkward restriction. We can insert the necessary static data as a separate post-deployment DML script, added to the DACPAC, but there is no User Interface to make it easy. We are at the level of .NET and DACfx, papering over the cracks. There are, of course, ways round this problem. For example, it isn't hard to create a view that, in its DDL script, contains data CREATE VIEW TheDaysOfWeek
AS
SELECT DayNumber ,
TheDayName ,
'Cornish' AS TheLanguage
FROM ( VALUES ( 1, 'dydd Sul'), ( 2, 'dydd Llun'), ( 3, 'dydd Mawrth'),
( 4, 'dydd Mercher'), ( 5, 'dydd Iau'), ( 6, 'dydd Gwener'),
( 7, 'dydd Sadwrn') ) f ( DayNumber, TheDayName );
GOYou won't be able to change the data without re-creating the view. This, of course, can get unwieldy and 1000 rows is a practical limit. However it is surprisingly efficient where the number of rows is reasonable. The point is that the data is in the source DDL of the object and so, again, the version of the data is automatically tied to the version of the database. However, though this helps 'enumerations' or 'reference data', this doesn't solve the problems of 'seed' data in SSDT. There are plenty of ways of circumventing local restrictions of what you can, or can't, do in the database build process, but it would be much better if these arbitrary restrictions weren't there in the first place. The build script must provide whatever is necessary for the data. If we understand the data, its restrictions, ownership, security, constraints, mutability, and so on in all its richness and variety, and deal with this in the build script, then so many such problems just seem to evaporate. Phil Factor. Phil Factor Join the debate, and respond to today's editorial on the forums |
|
| Featured Contents |
