Relational database
From DocForge
A relational database is a database in which information is stored in multiple linear tables. The relationship among sets of data is defined by the relationship between fields. A relational database can be contrasted with an object-oriented database which stores objects and identifies the relationship between objects based on their properties.
Contents |
[edit] Advantages
The benefits of a relational database over other methods of long term storage make it very popular for many situations.
- Most types of data can be organized in sets with multiple predefined fields. An address, for example, will contain a street, city, state, and zip code.
- Each record in the database can easily be given a unique identifier (id) to link to other records. An address record, for example, may be stored with a customer id to represent the relationship between a person and their addresses.
- Records with simple data types (integers, strings, floats, etc.) can be stored on disk or in memory with a fixed width, making retrieval and parsing fast and efficient.
- A standard query language, SQL, is supported by most relational database servers. This simplifies development and increases code portability.
[edit] Disadvantages
- Not all forms of a program's data are best represented in multiple related tables. Data expressed in a tree or hierarchy, for example, can become complex. While a structured document form such as XML might be convenient for representation and transmission to other applications, storage of the fundamental data in a relational database may be cumbersome.
[edit] Software
There's a wide variety of relational database servers, desktop applications, and programming libraries available.
- Client applications
- Libraries
- Servers
[edit] Database Design
Designing relational databases is a fundamental component of software development for many types of applications. It's critical that a database be designed well from the start because changes to it later can have far reaching implications in any applications that use it.
[edit] Best Practices
As always, the optimal database design is dependent on the situation in which it's going to be used. There are general best practices, or guidelines, which apply to almost all relational databases.
- Plan with as much foresight as possible. Database design typically should be scheduled relatively early in a project.
- Learn the domain. It's much easier to lay out data when the domain is completely understood.
- Write documentation.
- Normalize tables. Ideally each record in a table will represent one distinct item. This will reduce redundancy and help enforce referential integrity.
- Organize the data first based on the data itself, and second on how it's going to be used. A primary focus when first laying out tables should be how data elements naturally relate to each other. Proper organization will help build better queries, make applications easier to program, and help retain data integrity.
- Define naming conventions, similar to general coding standards. For example, name all fields in lower case with underscores for spaces, and end all fields which are identifiers with _id. The choices made are less important than the fact they need to be consistent.
- Create unique keys based on actual unique data, as opposed to arbitrary identifiers, when possible. For example, a product number in a product table should probably be unique. Using a separate guid as the only unique identifier would allow multiple products to have the same product number within the table.
- Use primary keys, foreign keys, and stored procedures to protect data integrity.
- Use stored procedures to also aid performance, security, and encapsulation.
- Follow SQL standards when possible. While each database system has additions and modifications, following the standard will increase code portability. As a database grows, for example, it may be best to move from one vendor to another, and this is much more easily accomplished if standards are supported and adhered to.
