Top 10 Tips For Database Design

The following are some of the tips that can help ensure that databases created that can be easily exported and manipulated with the minimum of difficulties.

  1. Develop A Prototype – Significant time can be saved by creating the structure in a simple desktop database (such as Microsoft Access) before finalising the design in one of the enterprise databases. The developer will be able to recognise simple faults and makes changes more rapidly than would be possible at a later date.
  2. Split database structure into multiple tables – Unlike paper-based structures, databases do not require the storage of all fields in a single table. For large databases it is useful to split essential information into multiple tables. Before creating a database, ensure that the data has been normalised to avoid duplication.
  3. Use understandable field names – The developer should avoid field names that are not instantly recognisable. Acronyms or internal references will confuse users and future developers who are not completely familiar with the database.
  4. Avoid illegal file names – It is considered good practice to avoid exotic characters in file or field names. Exotic characters would include ampersands, percentages, asterisks, brackets and quotation marks. You should also avoid spaces in field and table names.
  5. Ensure Consistency – Remain consistent with data entry. If including title (Mr, Miss, etc.) include it for all records. Similarly, if you have established that house number and address belong in different fields, always split them.
  6. Avoid blank fields – Blank fields can cause problems when interpreting the data at a later date. Does it mean that you have no information, or you have forgotten to enter the information? If information is unavailable it is better to provide a standard response (e.g. unknown).
  7. Use standard descriptors for date and time – Date and time can be easily confused when exporting database fields in a text file. A date that reads ‘12/04/2003’ can have two meanings, referring to April 12th or December 4th, 2003. To avoid ambiguity always enter and store dates with a four-digit century and times of day using the 24hr clock. The ISO format (yyyy-mm-dd) is useful for absolute clarity, particularly when mixing databases at a later date.
  8. Use currency fields if appropriate – Currency data types are designed for modern decimal currencies and can cause problems when handling old style currency systems, such as Britain’s currency system prior to 1971 that divided currency into pounds, shillings and pence.
  9. Avoid proprietary extensions – Care should be taken when using proprietary extensions, as their use will tie your database to a particular software package. Examples of proprietary extensions include the user interface and application-specific commands.
  10. Avoid the use of field dividers – Commas, quotation marks and semi-colons are all used as methods of separating fields when databases are exported to a plain text file and subsequently re-imported into another database. When entering data into a database you should choose an alternative character that represents these characters.

And DAMN it’s been a long time since I’ve put something here. I’m not forgetting about the site… just the new job has been taking quite a bit of time out of my leisure activities, this website is one of them. I made quick work of the dozens of spams that got through the filter and updated WordPress.

Also, another problem is I have started using Linux. I’ve been running a mix of Linux and XP at home lately to get a feel of the environment and hope to shift entirely to Linux on the day.

Let’s hope that the next post isn’t so far out. In fact, I promise the next thing I write will be sooner rather than later. 😉

Advertisements

6 comments

  1. Linux.. Good for you!

    The info on databases is good, though leaving fields blank is debatable. “Unknown”, N/A or N/K on a database can be problematic i.e. you’ll need to go through and strip all that junk out of it before you will be able to print addresses from the database. If it’s blank leave it blank.

    Regarding commas – they are often used as part of an address where the address does not necessarily fit into the requisite number of fields. Exporting data to CSV or text files should therefore include quotation marks around the fields as delimiters to avoid ambiguity.

  2. Hey, it’s great getting feedback from someone who keeps telling that she is not comfortable with computers and technology…

    Sometimes, while reading data back from the database can cause problems when the field doesn’t contain any data or a particular field is blank.. if you are feeding the data right into some data structure (Object) and then displaying from it onto some view then instead of putting some additional logic in the view, we can have that logic in the control, but again its additional work to do in the control. You have to check which field is blank. so, keeping some information like “Unknown” or “Not Available” would make the programmers life easy.

    I am trying to be elaborate in explaining things, and hope am not confusing you with the point I am trying to make..

    and let me know if I am wrong or confusing you.

    And thanks for the comment..

  3. Hi,

    I am Richa from SiliconIndia. I am also an avid blogger for a while now and participating actively in Indian blogosphere. I read your blog posting and found them very interesting and informative. We would love to see a copy of your blogs posted here, whenever you are posting it on blogger.com. Here are some of the benefits of posting your blogs here:

    We have a strong community of 500,000 Indian professionals
    Best blogs of 2008 to be published in a book “SiliconIndia bLoG PrinT”
    Best blog to be printed in SliconIndia & SmartTechie magazines each month
    Chance to be featured on homepage everyday

    We appreciate your community initiative here and in helping build a more powerful India! Also, if you have any ideas or want to volunteer to help for SiliconIndia, we would be more than excited to get your help. Pls mail me back at richa@siliconindia.com with your suggestions and feedback.

    Richa
    Blog Editor – SiliconIndia

  4. I don’t really understand your last answer Syed.. But thanks for trying to explain things to me.. I appreciate it! (I think the problem is that you’re talking about databases in programming design. I was talking about working on the front end of a database… By that I mean filling in the blank fields and making sure that everything is in the correct place and order… Which I imagine must to quite different to what you’re doing with a piece of code – it’s my fault don’t worry about it :))

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.