databasedev.co.uk - database solutions and downloads for microsoft access

Relational Database Design Planning

When you need to build a database, there is a temptation to immediately sit down at the computer, fire up your RDBMS, and start creating tables. Well, don’t. There is a process you need to follow to develop a well-designed relational database and, at the start, you’re a long way from actually setting up the tables in the database application. Not necessarily a long way in time, but certainly in thought.

A systematic approach to the design will save you, the designer, a lot of time and work and makes it much more likely that the “client” for the database will get something that fits the need. In this topic, you’ll look at the steps of a design process that you will follow.

When you get to the point of drafting your tables and fields, you’re going to use a very low tech approach to the design process—pencil and paper. You’ll find lots of blank spaces in this manual to work in. When you start building databases on your own, if you’re the kind of person who just cannot think unless you’re looking at a computer screen, there are software tools available for modeling a database. These CASE (computer-aided software engineering) tools can be used to create diagrams and some will create documentation of the design; they can be particularly useful when a team is working on the design of a database. Additionally, some CASE products can generate commands that will actually create the tables in the RDBMS.

The idea is to draw a picture of your tables and fields and how the data in the tables is related. These are generally called entity-relationship, ER, or E/R diagrams. There are various formal systems for creating these diagrams using a specific set of symbols to represent certain objects and types of relationships. At this point in your design career, you should probably use whatever works for you. Again, a formal system becomes more useful when a group of people are working on the same design. Also, using a recognized method is helpful for documenting your design for those who come after you. For additional information on ER diagrams, you may want to read Entity-Relationship Approach to Information Modeling by P. Chen.

The Database Design Process

1. Identify the purpose of the database.
2. Review existing database.
3. Make a preliminary list of fields.
4. Make a preliminary list of tables and enter the fields.
5. Identify the key fields.
6. Draft the table relationships.
7. Enter sample data and normalize the data.
8. Review and finalize the design.

Following a design process merely ensures that you have the information you need to create the database and that it complies with the principles of a relational database. In this topic, you're going to use this process to get the point of having well-designed tables and relationships and understand how you can extract data from the tables. After that, you, as the designer, may also have to create additional objects for the application, such as the queries, forms, reports, and application control objects. Most of those tasks are application-specific and are beyond the scope of this topic.

In this topic, you'll review an outline of the process. You'll go through the first few steps of identifying the purpose of the database and, in subsequent topics, will design the tables and relationships. You're the database designer and the information contained represents the client (the person(s) who has expressed the need for a database).

If you wish,you can work on a database of your own where you can be both the client and the designer. Then you have nobody to blame but yourself if it doesn't come out right.

So, where to begin?

1. Identify the purpose of the database.

   You will rarely be handed a detailed specification for the database. The desire for a database is usually initially expressed as things the client wants it to do. Things like:

   • We need to keep track of our inventory.
   • We need an order entry system.
   • I need monthly reports on sales.
   • We need to provide our product catalog on the Web.
   It will usually be up to you to clarify the scope of the intended database. Remember that a database holds related information. If the client wants the product catalog on the Web and sales figures and information on employees and data on competitors, maybe you're talking about more than one database. Everyone involved needs to have the same understanding of the scope of the project and the expected outcomes (preferably in order of importance). It can be helpful to write a statement of purpose for the database that concerned parties can sign off on. Something like: "The Orders database will hold information on customers, orders, and order details. It will be used for order entry and monthly reports on sales." A statement like this can help define the boundaries of the information the database will hold.

   The early stages of database design are a people-intensive phase, and clear and explicit communication is essential. The process is not isolated steps but is, to a point, iterative. That is, you'll have to keep going back to people for clarification and additional information as you get further along in the process. As your design progresses, you'll also need to get confirmation that you're on the right track and that all needed data is accounted for.

   If you don't have it at the beginning of the design process, along the way you'll also need to develop an understanding of the way the business operates and of the data itself. You need to care about business operations because they involve business rules. These business rules result in constraints that you, as the database designer, need to place on the data. Examples include what the allowable range of values is for a field, whether a certain field of data is required, whether values in a field will be numbers or characters, and will numbers ever have leading zeros. Business rules can also determine the structure of and relationship between tables. Also, it will be difficult for you to determine what values are unique and how the data in different tables relates if you don't understand the meaning of the data. The reasons will be clearer when you actually get to those points in the process.

2. Review existing data.
   You can take a huge step in defining the body of information for the database by looking at existing data repositories. Is there an existing database (often called a legacy database) even if it isn't fitting the bill anymore? Is someone currently tracking some of the information in spreadsheets? Are there data collection forms in use? Or are there paper files?
   Another good way to help define the data is to sketch out the desired outcome. For example, if the clients say they need a monthly report of sales, have them draft what they have in mind. Do they want it grouped by product line? By region? By salesperson? You can't provide groupings if you haven't got a field containing data you can group on. Do they want calculations done? You can't perform calculations if you haven't stored the component values.

   Your goal is to collect as much information as you can about the desired products of the database and to reverse engineer that information into tables and fields.

3. Make a preliminary list of fields.
   Take all you have learned about the needs so far and make a preliminary list of the fields of data to be included in the database. Make sure that you have fields to support the needs. For example, to report on monthly sales, there's going to have to be a date associated with each sale. To group sales by product line, you'll need a product line identifier. Keep in mind that the clients for a database have expressed their need for information; it's your job to think about what data is needed to deliver that information.

   Each field should be atomic; this means each should hold the smallest meaningful value and, therefore, should not contain multiple values. The most common disregard of this rule is to store a person's first name and last name in the same field.

   Do not include fields to hold data that can be calculated from other fields. For example, if you had fields holding an employee's hourly pay rate and weekly hours, you would not include a gross pay field.

   To be Continued…

   The rest of the steps in the design process will be addressed in subsequent topics. They are:

4. Make a preliminary list of tables and enter the fields.
5. Identify the key fields.
6. Draft the table relationships.
7. Enter sample data and normalize the data.
8. Review and finalize the design.

The Database Designer's Dilemma

As you think about the design of a database and work through the process, keep Motto #4 in mind. If five different database designers were given the same information, they would most likely come up with five different database designs. But that is all the more reason for following a process and complying with the design principles. The resultant design will be a valid design.