Top 10 Tips For Database Design

Sometime back I found a document which provides top 10 tips which can help to ensure that databases are created that can be easily exported and manipulated with the minimum of difficulties. Thought I should be sharing this with all of you.

The 10 Tips:

1. 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. 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. 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. 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. 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. 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. 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. 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. 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.

1 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 being 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 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 thoughts on “Top 10 Tips For Database Design

  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 )

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