While working on a Legacy Application with Legacy Database design as part of fixing a bug, I thought this bug would not have ever happened if a particular column was defined as Non Nullable since this particular column was the identifier to the parent table.
In development mode you don’t want to worry about which table goes into what Tablespace in production as it complicates development environments. The production DBA’s want to have their input and control over deciding what table goes into what Tablespace. To allow for this I used a mapping scheme as shown below.
Database designs I have seen tend to not constrain the data in the database. For example make the Item.ManufacturerID non-nullable and make it a foreign key to the Manufacturer table. Similarly Manufacturer.Name and Item.Rate as non-nullable columns. In any greenfield application (existing production application is a topic for another post). When you design table(s) lets say you have Item and Manufacturer table as shown below
Many a times Refactoring is talked about in the context of code, recently I finished working with Scott Ambler on Database Refactoring
Lately I have been working on changing data in an production database, and have been wondering how do I define it, Data Refactoring? what are the patterns of Data Refactoring. First let me talk about what I mean by Data Refactoring.