Database design: A good approch to keep data on Track

No Gravatar

Database is everywhere and kind of everything. Web application development mostly depends on a good database design. So we must be careful about it. Here I’m gonna tell you a good practice while designing database. Its not something mandatory but really good approach.

ID: everybody know about it. Better skip it.

CreatedDate: In every table we keep a new field that is ‘CreatedDate‘ which is gonna hold the date when the data is created. It is a good practice to keep the data on track.

LastUpdatedDate: this is gonna be a new field in every table, that will keep track about when data is last updated.

Version: It is very good idea to keep version. There is a term that is optimistic locking.

Let me tell you a scenario. For say, We are gonna make a decision depending on a piece of data. So we are gonna retrieve the data through our application first. In web application, we use connectionless protocol. So after getting response from web server, it gets disconnected. For say, we have the data now, and we are gonna make the decision. But somehow, after our data retrieval, someone has  updated the data. So we have the old data right now. If we make our decision with the old data, its gonna be wrong decision, right !

So there should a way to handle this type situation, and this is called optimistic locking.

Versioning implementation : make the field version ,long.  And increment by 1 every time when data is gonna be updated. When we are gonna make decision, check, is the previous version and the current version same? if same proceed, otherwise retrieve the data again.

Deleted: keep a boolean field deleted. A simple thing to remind, If a data enter into database, we are not gonna delete it by any means. We just change the flag, that this data is deleted or not. It is gonna minimize lots of complexity.

We are very much familiar with ORM tools, like hibernate in Java.
In Java we can have a abstract class like Persistence.


public abstract class Persistence {
 protected int id;
 protected Date createdDate;
 protected Date lastUpdatedDate;
 protected long version;
 protected boolean deleted;
}

and extend this class every time in your db class.


Bazlur RahmanAbout the Author: A. N. M. Bazlur Rahman is the editor of Vanquish Tech’s blog. He is a student of Institute of Information Technology of University of Dhaka of its software engineering program. He is very much interested in software development and working with php right now. He likes Java programming language very much. Through this web site, he wants to make a large community of software development especially web application development. He enjoys his pastime with programming, listening songs, blogging, watching movies and different computer stuff. You can follow him on twitter .

2 Comments

Leave a Reply

Your email is never shared.Required fields are marked *

This site is using OpenAvatar based on