Understanding Databases

Design Specifications for Databases

When you are first wanting to use a database, the developer or user has to determine what the database will be used for such as an inventory database, accounting database, a work order database, a recipe, a maintenance tracking database, or a web blog or other web based database driven application.

Once the usage has been determined; then the need to know the number of users that will be accessing the data and the number of transactions or records that the database will require is next.

These are the basic bits of information that are needed in order to begin establishing and defining the database design specifications. If a user is only going to need to track recipes then a low cost database solution would be an Access Database. If millions of records are needed to be stored, queried and processed then a work horse database like Oracle would be needed. For a business web based solution a Microsoft SQL database would be best suited for cost and the size of the database being queried.

Then the developer needs to determine the number of tables that would be required to meet the needs of the usage. For a contact database that could be just a single table. For a work order database it might contain many tables such as a contact table, company table, work order table, project management table, and a document management table.


Database Organization

A database contains tables, tables contains records like a rows, and records contain fields, and fields are of a certain data type like text or string, integer, Boolean, currency, date, etc.

To eliminate redundant data, like contact information the tables can be linked together and enforced referential integrity with one to many connections called relationships. These relationships are formed by connecting fields called keys. These keys are called primary and foreign keys.

Table relationships can also be established by quires to created views of the data, but have no underling effect on controlling the data in a relationship. This query action allows for powerful ways to exam and review data.

Some database applications make it very easy to integrate multiple databases together to share and exchange information, provide enhanced security and are highly scalable to meet the needs of tomorrow demands.


File Processing verses Database Management

File processing is an old method in which data is contained in separate files. A table for contact information would be in one file and inventory information contained in another file. There are two common methods of file access sequential and random. These determine how the records are organized within the file. Examples of this method are dBase III, Clipper, and DAT files. These are older database file formats. The biggest disadvantage of these types of files are with multi-user and high record transaction rates. They do not perform well and record locking events often occurs, thus preventing the update of data.

A database management system can better manage these types of issues. It consolidates all the tables that would normally be multiple files into a single file or database. It assists in the record transaction process and ensures better data integrity. It is the preferred method. Microsoft SQL has an excellent database management system (DBMS). Access has a decent DBMS and is great for less than 5 users for simultaneous access.


Database Security

Database security is extremely important. It is even more important when commerce is involved. Not everyone needs access to the same information and not everyone needs to modify that information. Security can be very strict or very relaxed. A web blog is built upon a database. It is commonly open for everyone to query, but no one has the ability to modify it. The University of Phoenix has student logon accounts. These accounts only allow the individual student access to his or her data. That data is contained in many different databases such as student records, grades, class information and accounting. However the single security logon allows the students to seamlessly traverse many database systems in a single interface or presentation.


Database Management in My Work Place

In my work place we use a variety of database systems. Our old work order database system was in Access. Now it is in ASP.NET for the web interface language running Microsoft SQL server for its database on Microsoft Windows 2003 servers. My security website is running PHP for the web interface language and mySQL on Linux servers. The University of Phoenix is running ASP Classic and .NET with several SQL servers.


Proposed Improvements

The different databases are used for a specific need. For example you would not want to use a Simi-truck to deliver a post card. Using the database that fits the need is wise economical decision. To improve this better inter database data exchange could occur. This is currently in the works with Microsoft and other database developers with XML. This is a standard language to aid in the exchange of data in an agreed format from one classical system to another with little effort. For example exchange data with and Oracle database and a MS SQL database with the drag of a mouse in a web page. Once we have reached that level new business to business uses with be taken advantage of for the ease of sharing information.