• Good database planning can speed up data entry

    by  • January 10, 2012 • Databases, The DMP Official Blog

    Preparation, preparation, preparation. It’s essential when you’re designing a new database. Although the temptation can be to just start entering data and creating new fields as you need them, pre-planning your database can really help to make it more efficient and save you time in the long run.

    Screenshot of database optionsThe first thing you want to do is to figure out exactly what information you want to store in your database. For example, let’s say we’re building a simple one to keep track of business orders. Step one is to sit down and think about all of the information you’re going to collect about each order. You’ll probably want to know the customer name, address, phone number, the date of the order, the items ordered, the number of items ordered, the cost, the due date and date of payment. Maybe you also need to keep track of when the order ships out. And you’ll probably also want to have a field for general comments in case there’s something you want to note about this particular order.

    Already just by planning this out, I can spot places to streamline the data entry — namely when it comes to customer information. Because you may (and would hope) to have repeat customers, why should you keep entering all their details for each individual order? The great thing with databases is that they allow you to have multiple tables and link these tables together. Here, I would suggest building a separate table for your customer information, and then assigning each customer a unique ID (most databases will create a unique ID or “key” for each row anyway). Now in your orders table, instead of typing in your customer information each time, you can just refer to the customer ID. Then by linking the two tables together through a “relationship” (database programs vary on how to do this, so you should check your instruction manual), you can pull in the customer information for each order. You only have to enter these details into the customer table once, and then you can use the ID to use them repeatedly in your orders table.

    You also probably only have so many products, and each individual product is always going to sell at the same unit cost and have the same details. Instead of needing to input this into your orders database each time, you can also set up a products table. You enter the details there, give each product a unique identifier, and then use the ID to refer to it in your orders table. Many more advanced database programs (such as Access) will even allow you to create drop-down lists from other tables, so you can choose from a list of your products whenever you’re entering a new order.

    Even if you’re using an existing database, you may want to step back and take a look at how you’re doing data entry. Are you typing the same information over and over again? If so, you should think about separating this into a new table so that you can enter it once and be done with it.