Database design in software development process

[Mohsin_G]

Hi All,

I've been reading a bit about the different approaches to the software development process such as agile methods and etc. I might of missed it but I can't remember seeing anything about involving the database design in the process. The database is an important part of the process. I've read in a database book I have that the database design should be parallel to the software design, is that true?

Is the class diagram essentially the database design? Where is the normilzation if thats the case. I'm very confused please help! I just want to know when and how we design the database in software development projects.

Your replies I thank you for in advance

-Mohsin

[+theblazingangel MVC]

Methods and design

Database design, falls under the design phase of the SDLC. With the "traditional" waterfall model, just as with all other aspects of design, the database design would need to be completed in comprehensive detail during this phase, prior to any development which is done in the subsequent implementation phase, as I'm sure you understand already.

When it comes to Agile, things get a lot more flexible. Agile itself is not a method, it's simply a set of values, which several methods comply with. One of the core components of Agile methods in general is iterative development. With iterative development, the system is broken down into smaller pieces, and small selections of these pieces are tackled in "iterations". Each iteration is like a mini software project in its own right. First a little planning is done, then design work, then implementation and testing (can be done together as test driven development (TDD)). The design is allowed to evolve over time. It is in these mini design phases that designs for the database will be created and evolve. The bulk of the design work may end up being done in the design part of the very first iteration, however this design does not need to stay fixed, it can evolve over time through the subsequent iterations.

The idea expressed in the database book that you've been reading, that "database design should be parallel to the software design", fits with the philosophy of Agile software development. With the "traditional" waterfall method, as I already described, the database design would have to be completed before absolutely any implementation work is done, and it should have been done in enough detail, and analysed so thoroughly that absolutely no changes are needed later on. This obviously can waste a hell of a lot of time; it is much easier and much more natural to allow design to be done in parallel to implementation; for it to be allowed to evolve.

Here are two books I recommend:

• Agile Experience design, by Lindsay Ratcliffe and Mark McNeil. This book talks about how design work fits into Agile methods in general. It's technically a web design book, but the essence of it if I remember correct should really apply to any and all design work, and I found it to be a good read.
  
• Extreme Programming Explained, by Kent Beck (second edition). This book explains XP, a popular Agile method. The author of the book is the creator of the method, and one of the founders of the Agile manifesto. In researching methods for my dissertation I found information on XP on the web to be pretty rubbish; the wikipedia stuff seems to be a mish-mash of things from both the first and second editions of the method/book, and with a lot of other material it's either out of date or difficult to tell or understand clearly. This book really helped me understand it (as it clearly should).
  

One thing you should understand with XP, is that unlike most (all?) other methods, there are no "phases". You just have a set of values, principles and practices, and it's down to you to simply use them effectively for any given project.

Database design documents.

Firstly understand that you've got two principle types of databases, relational databases and non-relational databases. Relational databases are not object oriented.

What documentation you produce can sometimes depend on the SDLC method you're following. SSADM for example, which I had to follow during part of my university course, explicitly dictates the use of DFD's and ERD's. As another example, WSDM defines documentation that you must produce, but does not actually define which exact models you use to produce them.

Understand that with Agile methods, a common misconception is that you produce no documentation at all. This is completely false, you produce only what you need to produce, and keep things as simple as you can. If an external client wants/needs a detailed ERD, you produce one and provide updated copies as things evolve. If they don't demand one, you may perhaps decide that you don't need to bother producing one at all. It depends on each unique project.

Something you should be aware of is UML, which is a standardised modelling language. UML is object oriented. UML can be used for modelling a database, however can have some disadvantages over an ERD. The following might be helpful: UML as a Data Modeling Notation, Part 1, Part 2, Part 3, Part 4.

"Class diagram" is probably referring to the UML concept.

Personally I would consider an ERD model to be the standard model to produce. However, I have little experience in database modelling in industry since I'm only just in my last year of university, and up to this point as a student I've always so far simply been told what models to produce, which has always been an ERD and sometimes additionally a DFD.

Normalisation

Normalisation is a set of rules for how to structure a database. In learning normalisation you might go through a normalisation "process", producing a database in 1NF, then 2NF, etc. Once you understand the rules though, from that point on you simply cut straight to it, you don't really think about normalisation, you don't go through a normalisation "process", you simply produce a database that is normalised. So no method or model is going to describe/require the use of normalisation, it's just simply a standard set of rules that you should follow in producing the database model.

I should however point out that in the real world, most if not all databases are never fully normalised. Full normalisation can take things too far and start becoming a problem, for example by making queries unnecessarily complex.

Were you asking whether normalisation fits in with an object oriented database though? If so, then I believe the standard rules of normalisation are specifically for relational databases only. Google "normalization of an object oriented database", e.g. this.

--

Edit: Search for "erd" in google images for a proper idea of what one looks like if you need to. The one on wikipedia isn't representative of erd's that I have personally produced.

[Mohsin_G]

Methods and design

Database design, falls under the design phase of the SDLC. With the "traditional" waterfall model, just as with all other aspects of design, the database design would need to be completed in comprehensive detail during this phase, prior to any development which is done in the subsequent implementation phase, as I'm sure you understand already.

When it comes to Agile, things get a lot more flexible. Agile itself is not a method, it's simply a set of values, which several methods comply with. One of the core components of Agile methods in general is iterative development. With iterative development, the system is broken down into smaller pieces, and small selections of these pieces are tackled in "iterations". Each iteration is like a mini software project in its own right. First a little planning is done, then design work, then implementation and testing (can be done together as test driven development (TDD)). The design is allowed to evolve over time. It is in these mini design phases that designs for the database will be created and evolve. The bulk of the design work may end up being done in the design part of the very first iteration, however this design does not need to stay fixed, it can evolve over time through the subsequent iterations.

The idea expressed in the database book that you've been reading, that "database design should be parallel to the software design", fits with the philosophy of Agile software development. With the "traditional" waterfall method, as I already described, the database design would have to be completed before absolutely any implementation work is done, and it should have been done in enough detail, and analysed so thoroughly that absolutely no changes are needed later on. This obviously can waste a hell of a lot of time; it is much easier and much more natural to allow design to be done in parallel to implementation; for it to be allowed to evolve.

Here are two books I recommend:

• Agile Experience design, by Lindsay Ratcliffe and Mark McNeil. This book talks about how design work fits into Agile methods in general. It's technically a web design book, but the essence of it if I remember correct should really apply to any and all design work, and I found it to be a good read.
  
• Extreme Programming Explained, by Kent Beck (second edition). This book explains XP, a popular Agile method. The author of the book is the creator of the method, and one of the founders of the Agile manifesto. In researching methods for my dissertation I found information on XP on the web to be pretty rubbish; the wikipedia stuff seems to be a mish-mash of things from both the first and second editions of the method/book, and with a lot of other material it's either out of date or difficult to tell or understand clearly. This book really helped me understand it (as it clearly should).
  

One thing you should understand with XP, is that unlike most (all?) other methods, there are no "phases". You just have a set of values, principles and practices, and it's down to you to simply use them effectively for any given project.

Database design documents.

Firstly understand that you've got two principle types of databases, relational databases and non-relational databases. Relational databases are not object oriented.

What documentation you produce can sometimes depend on the SDLC method you're following. SSADM for example, which I had to follow during part of my university course, explicitly dictates the use of DFD's and ERD's. As another example, WSDM defines documentation that you must produce, but does not actually define which exact models you use to produce them.

Understand that with Agile methods, a common misconception is that you produce no documentation at all. This is completely false, you produce only what you need to produce, and keep things as simple as you can. If an external client wants/needs a detailed ERD, you produce one and provide updated copies as things evolve. If they don't demand one, you may perhaps decide that you don't need to bother producing one at all. It depends on each unique project.

Something you should be aware of is UML, which is a standardised modelling language. UML is object oriented. UML can be used for modelling a database, however can have some disadvantages over an ERD. The following might be helpful: UML as a Data Modeling Notation, Part 1, Part 2, Part 3, Part 4.

"Class diagram" is probably referring to the UML concept.

Personally I would consider an ERD model to be the standard model to produce. However, I have little experience in database modelling in industry since I'm only just in my last year of university, and up to this point as a student I've always so far simply been told what models to produce, which has always been an ERD and sometimes additionally a DFD.

Normalisation

Normalisation is a set of rules for how to structure a database. In learning normalisation you might go through a normalisation "process", producing a database in 1NF, then 2NF, etc. Once you understand the rules though, from that point on you simply cut straight to it, you don't really think about normalisation, you don't go through a normalisation "process", you simply produce a database that is normalised. So no method or model is going to describe/require the use of normalisation, it's just simply a standard set of rules that you should follow in producing the database model.

I should however point out that in the real world, most if not all databases are never fully normalised. Full normalisation can take things too far and start becoming a problem, for example by making queries unnecessarily complex.

Were you asking whether normalisation fits in with an object oriented database though? If so, then I believe the standard rules of normalisation are specifically for relational databases only. Google "normalization of an object oriented database", e.g. this.

--

Edit: Search for "erd" in google images for a proper idea of what one looks like if you need to. The one on wikipedia isn't representative of erd's that I have personally produced.

Hi theblazingangel,

Many thanks for taking the time to respond to me. Your post was really helpful. So basically I will be designing software in Visual studio (visual c#). An object oriented language. Now when it comes to designing the database I will probably develop some diagram in UML format as you have mentioned it is object oriented.

What does the design process involve for object oriented databases? Does it involve analysis of the business first (understand how the business operates, it's key objects and different business processes and things like that?) Basically do I base the database design on a set of facts or a description of the business and so develop a UML based diagram based on the facts? So I should first understand the nature of the business? (interview employee's etc)

Is there a set procedure I should be following like a series of steps when designing the database? Or can I just jump straight into making a UML based diagram? Also is one UML diagram sufficient as a database design which can then be implemented and created?

I found this process on the web for designing simple databases, can this process be used for object oriented databases?

http://office.microsoft.com/en-us/access/HA012242471033.aspx

What about the database technology offered in Visual c# is that relational or object oriented? And do we query it with SQL?

Thanks in advance,

-Mohsin

[Mohsin_G]

Can I develop relational databases in visual c#? I am reading a book on Visual c# and object oriented concepts and nowhere does it talk about designing a database. The closest thing it relates to a database is a class diagram which includes attributes, object names and behaviors or methods. Is it the case that a UML diagram is essentially the design of a database? It also talks about sequence and activity diagrams. (strangely it doesn't mention anything about testing :( which is why i am going through another book as this one seems incomplete to forget such an important topic.

[+theblazingangel MVC]

So basically I will be designing software in Visual studio (visual c#). An object oriented language. Now when it comes to designing the database I will probably develop some diagram in UML format as you have mentioned it is object oriented.

What does the design process involve for object oriented databases? Does it involve analysis of the business first (understand how the business operates, it's key objects and different business processes and things like that?) Basically do I base the database design on a set of facts or a description of the business and so develop a UML based diagram based on the facts? So I should first understand the nature of the business? (interview employee's etc)

Is there a set procedure I should be following like a series of steps when designing the database? Or can I just jump straight into making a UML based diagram? Also is one UML diagram sufficient as a database design which can then be implemented and created?

I found this process on the web for designing simple databases, can this process be used for object oriented databases?

http://office.micros...2242471033.aspx

What about the database technology offered in Visual c# is that relational or object oriented? And do we query it with SQL?

The programming language you're using is completely irrelevant here! Also, the fact that your programming language is object oriented does not mean that your database must also; in fact every single database package I have ever personally heard of is a relational one.

List of OO DBs, List of relational DBs

What might possibly matter here, specifically in relation to the database design documents, is whether the database you'll be using is a relational or an object oriented one (don't just pick one of these two options out of the blue, go and look into actual database software and choose something). With a relational database, an ERD will probably be the best document for you to produce. UML can be used in place of an ERD, but as the article I pointed to in my last post describes, it is not ideal. With an object oriented database, a suitable object oriented model should be produced, which UML would be perfectly suited for. Using an ERD for an object oriented database would, I imagine, not work.

Understanding the business and the requirements of the system is part of systems development in general. In building a system you will gain a high level of understanding of the business and the system, and work your way down into detail. A variety of different models describing the business/system may be produced to capture this knowledge, which serves as proof to the client that you truly understand it correctly, and to provide developers something to check their work against from time to time. The initial models will capture things at a very high level, and later models will describe certain components of them in more detail, ending up with all of the detail necessary in order to build the system. DFD's are high/mid level, they capture the flow of data in a system, and the diagrams can include people and external systems that interact with the system in question. (A DFD is not specifically a database design document, it captures the entire system). DFD models can be produced in several levels of detail, so you may have multiple. An ERD captures a detailed design of the structure of a database, in other words it's the detailed blueprint for the database. Being a detailed, low level design document, it must be built after the higher level models have been produced, or at least after that knowledge is gained (if actual models of it are not being produced).

The SDLC method being followed, the client, and you the developer(s), all have an input as to what models are actually produced for a given project. It may be that for a particular project you don't produce any models at all.

A single document (e.g. single ERD) may be sufficient modelling for the database in a system, yes. Understand that you may end up producing several versions before you settle on a final design, particularly if following an Agile method.

The webpage you linked to is providing information pertaining to relational databases.

The database functionality provided in any given language simply provides an interface to a database package. It is the database package itself that is either relational or object oriented, not the language's database interface. Note that the existence of an interface for database connectivity in a programming language does not automatically mean that the language in question can connect to any and all database packages in existence.

Whether SQL can be used depends on the database package.

Can I develop relational databases in visual c#? I am reading a book on Visual c# and object oriented concepts and nowhere does it talk about designing a database. The closest thing it relates to a database is a class diagram which includes attributes, object names and behaviors or methods. Is it the case that a UML diagram is essentially the design of a database? It also talks about sequence and activity diagrams. (strangely it doesn't mention anything about testing :( which is why i am going through another book as this one seems incomplete to forget such an important topic.

Are you actually asking whether relational databases can be used with an object oriented C# application? If so then I've just answered that above (ans=yes!). Your question sounded like you were enquiring about creating an actual DBMS package itself in C#...

UML is a modelling language, not a single specific model/diagram.

"also talks about sequence and activity diagrams"... there are many different models and diagrams that could be produced for a system...

"strangely it doesn't mention anything about testing"... The book sounds like it's teaching you very specifically the C# programming language itself. Testing is an entire topic in itself and I could forgive such a book from not including any sort of in depth discussion of it, though I'd expect it to at least briefly mention it. There's lots of stuff that falls under testing, such as: developing test plans; white-box and black-box testing; nightly/alpha/beta/rc/stable releases; automated unit testing; and test driven development (TDD, an Agile practice).

[Mohsin_G]

Hi theblazingangel,

Thanks for your response. Based on what you have told me and also my own research. I would like to develop some software for a business I am starting and I was wondering if you could take a brief look at the process I am going to follow to achieve my goal and provide any pointers or corrections to my understanding.

My intention is to perform the following steps as mini projects and apply the values of the agile methodology.

1. Develop Software requirements specifications document (SRS document)

2. Develop use cases and diagram them

3. Develop a class diagram (UML) (this automatically includes modelling the database via the uml class diagram If I have understood you correctly UML should be a blueprint for the database?)

4. Develop sequence diagram

5. Develop an activity diagram to help model the user interfaces

6. Develop User interface design

7. Develop Code

8.Test

Iterate

The following values of the agile methodology will be applied:

* Active user involvement

* Iteration thus small and regular releases

* Completion of each feature before moving on to the next

* 80/20 rule (Pareto's law)

* Early and often testing continuously throughout the project and not leaving it till the end as the waterfall model suggests

Am I on the right track?

Kind Regards

-Mohsin

[+theblazingangel MVC]

In principle it is a little difficult to give out proper recommendations without knowing anything about the business, the project and the people involved. However...

Firstly, use a relational database, not an OO one. As I said earlier, I don't recognise even a single product name out of the OO ones listed in the link above and my impression is that they are rarely used. Using a relational database you should therefore create an ERD, not a UML diagram for the database.

Secondly, and most important of all, Keep things simple!

• In creating design documents/models, only create whatever will help you, give you what you need and nothing more.
  
• Create your diagrams free-hand. Don't waste time trying to use a software package to produce something "neat".
  

You say that you will be developing this for a business that you yourself are going to start, therefore I take it that you're the only person involved in its development (aside from user testing/feedback)... So are all of these documents you've listed really necessary? . The 'class diagram' in particular, assuming that you're suggesting modelling the code itself, sounds like an absolutely horrible idea to me!

Understand that non-agile SDLC methods tend to have a big up-front design phase prior to implementation, where significant time is invested in creating thorough, detailed design documents/models. Some of these methods will require specific documents, and these documents may be written in specific formal formats, with significant detail, produced "neatly" with the aid of software packages. With Agile, things are kept simple and flexible. Documents do not have to be formal and "neat", and there is no fixed set of documents that must be produced. The research you've done I'm sure has helped you, but can also be a hindrance too. From what you've learnt, just take from it all a simple general idea of the type of information and interactions that all of these models try to capture, then think about what you feel you would benefit from capturing in an actual document/diagram, and capture it.

Here's a simple Agile process that you could perhaps follow:

1. Concept: Start by setting out a vision. Write down in simple terms, a short vision of what it is you want to achieve/build with this project. This provides a purpose and direction for everything else you'll do. For example, as described in the 'Agile Experience Design' book I linked to earlier (p33) when Google was founded, the vision was "to make it easier to find high-quality information on the web", as simple as that.
   
2. Requirements capture: Produce a story map. This will be explained shortly!
   
3. Implementation: This should be done iteratively. In each iteration:
   
   • Select a collection of stories to tackle
     
   • Do any analysis and design work necessary
     
   • Write and test code
     

You may have noticed that I have not listed a specific stage for interface and database design. Understand that with non-Agile methods, the design process is done entirely before the implementation, but design work being done in isolation from developers can easily result in bad designs. Additionally, one of the core principles of Agile is that the solution is allowed to evolve over time, rather than being built from a fixed detailed specification. Hence, interfaces and databases should be developed alongside developers in a collaborative manner, and developed over the course of the iterations of implementation.

The 'story map' (derived from the 'agile experience design' book mentioned earlier):

1. Start off by generating a set of 'personas'. Each persona will represent a different type of user that will interact with the system, and list their goals. For example, a standard customer of a bank may have a goal of 'check if salary has been paid into their account'.
   
2. From this, a set of unique goals will be established and written on index cards. You might also determine multiple solutions for certain goals, for example with the above goal, solutions include: checking their balance on the website; checking their balance through a mobile device; and setting up a 'salary payment received' alert (email/sms-text-message/whatever). Here you will decide what is in scope and what is not. Lay out the goals side by side, with the most important on the left.
   
3. Break down each goal into the 'activities' necessary in order to complete it. These activities should be laid out from left to right underneath the goal they belong to.
   
4. Certain activities such as 'login' might obviously be repeated under many goals, so could be moved out to become a goal in its own right (perhaps labelled 'prove to bank who I am' in this case).
   
5. For each activity, break it down into tasks, aka 'user stories' (because they can be described as 'As X, I want to Y, in order to Z'). For example, for an activity of 'view transactions', these may include: 'see payee date and amount'; 'see depositor date and amount'; 'sort transactions'; and 'search for transaction'. These should be laid out one on top of another (most important near the top), underneath the activity they belong to.
   

• Customer 'journeys' through the system can be overlaid on top of this using string.
  
• I already mentioned the use or index cards, both these and sticky notes are commonly used.
  
• Story maps are often very large and done on a wall.
  
• The entire collection of 'user story' cards represent your backlog of work.
  
• In each iteration, you will choose a selection of 'user stories' to tackle.
  
• At the end of each iteration you should have a piece of working software.
  

You should get user feedback throughout the process, it is invaluable.

Of critical importance if you want this to be a successful project is to manage scope creep carefully.

[+theblazingangel MVC]

Firstly, use a relational database, not an OO one. As I said earlier, I don't recognise even a single product name out of the OO ones listed in the link above and my impression is that they are rarely used. Using a relational database you should therefore create an ERD, not a UML diagram for the database.

Actually, I've just discovered that there's a class of 'object-relational' databases that form a middle ground between fully relational and fully object oriented. PostgreSQL is one example and is well known and very well respected. Oracle is another, but Oracle can be very expensive as I'm sure you're aware. If you go in this direction, then ERD and UML database diagrams might both be equally suitable, your choice.

[+Majesticmerc MVC]

Secondly, and most important of all, Keep things simple!

• In creating design documents/models, only create whatever will help you, give you what you need and nothing more.
  
• Create your diagrams free-hand. Don't waste time trying to use a software package to produce something "neat".
  

That reminds me of THIS (warning: foul language).

These are great posts dude, I'm actually saving this thread for later reading :p

[Mohsin_G]

Hi theblazingangel

Thanks for you responses, they were very helpful.

I just have a question. Keeping things simple is a natural and easy way to manage things I certainly agree with you. Can that also apply to testing? I wanted to do functional (black box) testing on my application. But I'm a bit confused as to exactly what that involves. Does that simply involve executing the program and testing it against different scenario's (can I say test cases?). For example lets say I have an application that has a function of adding a new customer to a database. Can I say functional testing would involve loading the application, entering in customer details, clicking the save button and seeing if the customer was added to the database successfully? Is that how simple functional testing is? I don't need to test or know the internal code I just want to simply test the functionality of the application and if it does what it is supposed to do. I just want to follow a simple process. Can you give me a simple example of functional testing and what it involves and a test case.

Thank you so much for sharing and taking the time to respond to my posts I really appreciate it.