In the previous post we learned how software design is done on agile projects. Now let us think about database design for software products on agile projects.
Like software design for agile projects, database design is also done after the customer gives software requirements to the project team to develop a bunch of software product features. Thus in each iteration of software product development activities, database design is also a part which need to be developed. This means that a part of database design is done after many parts of the software products (including their respective database design) are already done. How we can cope with such a scenario?
Changing database design is more complicated than changing a software design. It is because databases entities can not be changed easily as database rules are applied very firmly. For example suppose you have a master table where you keep information about various kinds of menus for a restaurant. Now if you change structure of this table then existing data in the table will become invalid. So you may need to delete all existing data and then re-populate data for the newly re-structured table. For practical reasons like supporting existing data, this change in the table structure may not be possible at all. Many existing customers may have their data and all these pieces of data may become invalid with change in table structure.
In such a scenario what could be a good way to create database design for the new entities which need to appended to existing database structure or an existing database entity needs to be changed to accommodate database entities for new software product ?
The best approach is to divide all new database design into 2 parts: the design parts which involve changing existing database structure (thus affecting existing data) and the design parts which do not affect existing data (no change in existing database structure). The design part which does not affect existing data can be easily designed. When it comes to change in existing database structure then a compromise can be done. instead of changing database structure, some duplication in data can be allowed to happen. For example when we increase the menu types from 4 to 10 and decided to create sub-classes for these menu types in the previous post, we can change our database design as well to reflect this changed structure in database design. If currently there is a menu type entity (table) in the database and details about each menu type is described in each record in the table (chicken, salad, long bread, mustard sauce etc.) then you can just create some more records in this table for new menu types. There could be some duplication of data over records but this is fine. But imagine you have to create a separate table for each menu type!. A whole lot of database structure will need to be changed here. At the same time, the programming code which accesses the database will also need to be changed accordingly.
Decision regarding the change in database design will depend on future planning as well. If the project team knows that a part of the software product is not going to be developed much in future then investing too much time in database design and subsequent changes in programming code will be a waste of time. But if a lot of development is still needed in that part of the software product then definitely it calls for a change in database design early.