|
Database design methodology - I Now that we have discussed the various methods of accessing data, let us see how to design a database with an emphasis on performance, functionality, future expansion, three-tier architecture and web-oriented programming. This methodology is essentially an improvisation of the traditional design methodology. The steps outlined here are a set of tried and tested guidelines. However, you can (and you should) customise it to your own requirements. During this discussion, we will also cover the application and advantages of utilising a Case tool for designing databases.
Practical database design
The need
The job of the analyst designer involves understanding user requirements and translating these into a system design. There are many ways and means of doing this. Given here is a practical and effective approach to good database design. Individual aspects of design will be covered in additional detail in further articles in this series.
Scope of design
Database design is no longer the same as simply defining tables, attributes and stored procedures. As many new methodologies of application development like web programming, three-tier architecture have emerged, the need of altering the design methodologies has grown.
Today's database designer should consider the following:
Base entities, attributes, and relations.
Enlisting queries and performance parameters.
Index design.
Stored procedures for encapsulating access control.
Stored procedures for encapsulating common modular functions/processes.
Stored procedures for OLE/COM integration.
Custom triggers for data event driven actions.
Stress testing and query optimisation.
The problem
Data is crucial to any application. The way in which data is designed affects not just the performance but many other related areas. Poor design can affect:
Maintainability.
Performance (especially when the data volume increases).
Complexity of the front-end code required to access the data.
Extensibility of the data structure to accommodate future changes.
The design process
The design process initially starts at a conceptual level. The data structures are never directly specified by the end-users. Once the conceptual model has reached an acceptable level of maturity, you can think about the physical implementation. Physical implementation relates to defining the field names, data types and so on.
Understanding user requirements
This phase occurs while you are interacting with the customers to evaluate their needs. As you discuss the requirements, conceptual data requirements emerge.
Create logical entities
The entities at this stage are purely conceptual. These entities may later get converted to actual tables in the database. However, at this stage you do not need to bother about the target database, actual table names, field names and data types. Here we are only working on the functional representation of data entities.
Define the entities in plain English
One of the most important steps, something that is often ignored, is to define each entity in plain English. This may sound like it's a trivial and unnecessary step, but it is not. Consider a manufacturing management system. After discussions with the end-users you arrive at the conclusion that you need an entity called Product. Now this looks fairly obvious. So you would normally define the entity Product as "All items manufactured by the company". When you show this to the end-user, however, you realise that entity includes even the by-products or intermediate products, as well as scrap generated in the manufacturing process. Thus it is important to define each entity in plain English, in one or two sentences. Try it with some existing tables or on a new project and see how it's often difficult to accurately describe a conceptual data entity!
Verify your definitions with the end user
The next step is to ask the end-user to take a look at the definitions you have created and get them validated. This step clarifies deficiencies in understanding or misinterpretations at a very early stage in the project development cycle. This prevents costly patches and changes to the software at a later stage. This list of simple English definitions also servers as a useful glossary for the developers, system maintenance personnel, documentation team, and anyone who enters the project at a late stage.
Evolve attributes
Now you can start evolving important attributes for each entity that you have created. These attributes can also be initially defined in English. Do not think about the actual field names at this stage. The logical names are easy to understand in English.
Sources of attributes
Various types of characteristics, quantities and qualifiers about entities are termed attributes. For example an entity called a computer may have attributes like processor type, keyboard type, hard disk capacity, number of ports available, memory available, display resolution and so on. As we discuss user requirements, the attributes can be evolved for each entity. Unfortunately, all the attributes about a given entity may not evolve from discussions with the users. You must gather attributes from other sources too. Here are some sources that are very useful.
Look at existing applications, if any, which are used for the same purpose. Existing applications may be old, incomplete or not in use at all. However, you must understand that some amount of analysis must have gone into making that application. Thus the data structures available in the older software is definitely useful.
Existing or expected reports. Reports are required out of almost all applications. In the absence of an application, reports are generated manually. There may even be printed formats that are manually filled up by users. In some cases users provide a layout of the expected report format. All these reports will provide you with the attribute requirements for entities.
A very common mistake in the application development cycle finding out that some particular information required to generate a report is simply not being captured in scope of the current data design. If this is noticed at a very late stage in the project development cycle, the cost of correcting this is very high.
Think. Just thinking about the entities and their functional usage can lead you to discovering some attributes that have not surfaced by any other method.
Plan future requirements. On many occasions, users tell you that they want a particular functionality in the application, but at a later date. Although the user level features may be incorporated later, you can still design the data structures now. This provides for simpler implementation of the enhanced features.
Create and define attributes
As with entities, attributes need to be defined in plain English. This eliminates ambiguous and equivocal items and clarifies the meanings at a very early stage.
Create a glossary for your application
One of the simplest rules to explaining a difficult word is that the explanation itself should not contain another difficult word. While defining entities and attributes, you may come across some complex terms that are specific to the business need, application or the design. It is a good practice to create a glossary of such terms. This can be done while you are defining entities and attributes. This glossary has multiple advantages.
Whenever you are validating the data design definitions with users, you can attach the glossary for increasing the level of comprehension.
For developers, maintenance personnel, testing and documentation personnel, it is important as this glossary provides basic knowledge and understanding without any discussion with the user or system analysts.
Another team may need to understand the application quickly at a later date, in order to enhance the application and maintain it. This may occur in 1 month or 4 years. A glossary coupled with entity and attribute definitions is the fastest way to understand the basics of any application.
Validate attribute definitions with end-users. As with entities, you must cross check the accuracy of the attribute definitions. Attributes are more difficult to cross check than entities, as users may not intuitively understand attributes. System level attributes like status indicators, flags, computed fields and keys are difficult for end-users to understand. If necessary, you can eliminate such attributes from the list before submitting the list to the users.
Data Design Tools
As it is quite apparent from the ongoing discussion, the data design process is complex and time consuming. If you design on paper, it becomes increasingly difficult to manage the complexity without wasting time in co-ordinating pieces of paper and linkages. Therefore, the need for data design tools has evolved. For the purpose of illustration, I will use Erwin Logic Works--a Case tool which manages logical as well as physical design aspects for most commonly used database engines.
This tool allows you to define entities, attributes and follow many of the steps outlined below. It provides for the same data design to be viewed in different ways. For example, you can view the definitions and then switch to attributes view. Here is how the entities would look at this stage. In this sample there are only three entities--Customer, Product and Order.
This is the 'definition' view. You can shuttle to other views while retaining the linkage between views. Here is the 'attribute' view. The view has another entity that has been added called 'Order details'. The list of attributes is yet not complete in this example.
The space above the horizontal line is still empty. The primary key is shown above the line. You will notice that the relationships are still not complete. As the diagram evolves, the attributes will be refined. Initial entities may be split or merged and additional entities will be added.
Specify primary keys
Now is the time to find out how each row in the entity will be identified. Primary keys are unique identifiers for each row. Primary keys may be apparent in the existing attributes or may have to be added specifically. It is important to ensure that primary keys are kept as thin as possible. Primary keys that contain more than one attribute affect performance.
Evolve and define relationships between entities
Dependent entities can now be marked and their relationships with other entities can be established. The purpose of this exercise is to provide a summary of how the keys will affect each other. Relations also need to be defined in English. The primary key of the main entity becomes the foreign key of the child entity. This typically is done manually. When you use a Case tool, the foreign keys migrate automatically.
|
