Home > Green IT, Greener Coding > Efficient Database Design – Tips!

Efficient Database Design – Tips!

Information Overload!  I have lots of useful information gathered on Green IT and environmental topics which I will share in a number of ways.

Database Overload? As a customer, I had someone recently tell me about their slow database. Patience.

What is the cost of having slow systems and incorrect data held in IT systems?

I hope the table below will help people understand what simple steps to take to ensure an efficient database is designed.

NO STEPS TO TAKE DESCRIPTION/EXAMPLE
1 Group Data Logically You will firstly want to group data into ‘tables’ (Techie Speak). Use a diagram!
  1. Table – Countries
  2. Table – Cities
2 Identify Data Relationships A city exists in a country. Bear in mind, one city can exist in more than one country.
  1. City – Coventry
  2. City – Reading
3 Ensure you are able to view who has maintained the data. Of course everyone uses their own logon details…YES WE DOIt helps to identify the person who originally created the data/document. Think of security and trust as well.
Who (have a table to store contact details)

  • Created it
  • Updated it
  • Viewed it
  • Deleted it

When was it (date/full timestamp)

  • Created
  • Updated
  • Viewed
  • Deleted
4 When data is actually stored in the IT system/database, ensure unique numbers are used (increment this automatically) to identify the record. Change will happen! You may want to display the record in a drop down list or Radio buttons on front-end/data-entry screens.
Milan and Milano (the same)
5 Use synonyms (database) and have descriptions. MIL (Milan) and MLN (Milano)
6 Ensure there is validation (on the database or in the code) to avoid duplicate records being created. Prevention is better than cure.
Milan MILan MILAN
7 The person who is writing the code (developer/programmer) will be linking back to the database. Try to build code dynamically such that you have one piece of code and are passing parameters to it. You may want to change the currency exchange rate for each country on a regular basis and will therefore want one command to update the data/record
UPDATE (parameter 1 passed) SET COLUMN 1= (parameter 2 passed)
WHERE COLUMN 2= (parameter 3 passed)

There’s lots more to consider – large Media files, Archives, audit trail, etc. Hopefully this helps!

Whether it’s Oracle or Business Objects (any business intelligence/database/CRM/reporting tool), I would advise investing time in understanding what data is being stored and for what purpose.

As with most things, review what you’re doing at least once a year. Prioritise with all projects/tasks you have – time, budget and people available.

And remember, there’s always a temporary solution/workaround before the perfect solution is implemented!

Keep it simple…

Author: Tripta Prashar
Company: Giving Time and Solutions Ltd
Company website: http://givingtimeandsolutions.com
Professional profile: http://linkedin.com/in/triptaprashar
Date created: 11 December 2012 2.00pm
Date updated: 2 April 2015 4.45pm

Advertisements
Categories: Green IT, Greener Coding
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: