DATABASE DESIGN

The key to an effective database is its initial design. In a well-designed database, data can be flexibly manipulated to produce timely, meaningful, and accurate information for decision making. Bad database design can lead to messy database, lost records, and inaccurate data. The goal of database design is to store information so that it easy to access and maintain, but concise enough to take up as little disk space as possible.

The term database structurerefers to the arrangement of fields, tables, and relationships in a database. The first step in structuring a relational database is to determine what data must be collected and stored. To do so, a database designer might begin by consulting users and studying the current filing system to compile a list of available data as well as any additional data necessary to produce on-screen output or printed reports.

After the designer determines what data to store, the next step is to organize that data into fields. It is usually easy to break data into fields just by using common sense and considering how people might want to access the data. Any data that people would to search for, sort on, or use in a calculation should be in its own field. The treatment of first and last name illustrates the concept of breaking data into fields. A database designer could definea field called Name to hold an entire customer’s name. With the entire name in one field, however, the database would not be able to access individual parts of the name, making it difficult to alphabetize customers by last name or to produce a report in which names appear in one field. That’s why the last names are stored in a different field than first names.

Although two people might have, for example, the same name or two paychecks might contain the same amount, a computer must have some way to differentiate between records. A primary keyis a field that contains data unique to a record.

The data that can be entered into a field depends on the field’s data type. From a technical perspective, data type specifies the way data is represented on the disk and in RAM. From a user perspective, the data type determines the way data can be manipulated. When designing a database, each field is assigned a data type. Data can be broadly classified as numeric or character. Character data contains letters, numerals and symbols not used for calculations. Numeric data contains numbers that can be manipulated mathematically by adding, averaging, multiplying and so forth. The are several numeric data types, including real, integer, and date. The real data type is used for fields that contain numbers with decimal places- prices, percentages, and so on. The integer data type is used for fields that contain whole numbers-quantities, repetitions, rankings, and so on. The date data type is used to store dates in a format that allows them to be manipulated, such as you want to calculate the numbers of days between two dates.

The text data type is typically assigned to fixed-length fields that hold character data- people’s names, albums titles, and so on. Text fields sometimes hold data that looks like numbers, but doesn’t need to be mathematically manipulated. Telephone numbers and ZIP codes are examples of data that looks numeric, but should be stored in text fields. A memo data type usually provides a variable-length field into which users can enter comments. The logical data type (sometimes called a Boolean or yes/no data type) is used for true/false or yes/no data using minimal storage space. Some file and database management systems also include additional data types, such as BLOBs and hyperlinks. A BLOB(binary large object) is a collection of binary data stored in a single field of a database. BLOBs can be just about any kind of data you would store as a file, such as an MP3 music track. The hyperlink data type stores URLs used to link directly from a database to a Web page.

The information produced by reports and processing routines is only as accurate as the information in the database. Unfortunately, data entry errors can compromise the accuracy and validity of a database. When designing a database, it is important to think ahead and envision potential data entry errors. Preventing every typographical error is not possible. However, it is possible to catch some of these errors by using field validation rules, list boxes, or lookups. A process called normalizationhelps to create a database structure that can save storage space and increase processing efficiency. The goal of normalization is to minimize data redundancy – the amount of data that is repeated or duplicated in a database.

Records in a database can be organized in different way depending how people want to use them. No single way of organizing the data accommodates everyone need’s, but tables can be sorted or indexed in multiple ways. A table’s sort order is the order in which records are stored on disk. Sorted tables typically produce faster queries and updates. In a sorted table, new records are inserted to maintain the order. Most DBMSs use a sort key to determine the order in which records are stored. A table’s sort key is one or more fields used to specify where new records are inserted in a table. A database index can be used to organize data in alphabetic or numeric order. A database indexcontains a list of keys, and each key provides a pointer to the record that contains the rest of the fields related to that key.

Designing the database user interface can be a challenging task. If a company’s database includes multiple tables used by many different people, a professional user interface designer usually creates and maintains the user interface. Large databases might even require a group of user interface designers, meanwhile the interfaces for smaller databases, such as those used by small business or individuals, is most likely created by a single one.

A well-defined user interface for a database should be clear, intuitive, and efficient. A designer might consider the following principles:

- Arrange fields in a logical order beginning at the top-left of the screen. The first field should be those used most often or those that come first in the data entry sequence.