Databases - Advanced Level Lesson 1

There are many types of databases. Many think of Microsoft Access when running databases, but you also have MySQL databases that are typically used by blogging software such as WordPress. You also have programmer created databases in many formats, many of whom are based on open source in the sense that you can convert to other formats using a comma-separated format. Horses for courses. What fits one type of program may not fit another.

We won't get into each format, but will try to look at overall principles relating to databases.

• Search types
• Optimization
• Pointers

Search Types
Back in the old days, you had several types of access to databases, and a very important aspect of running a database was regular sorting of these databases. When you only had 80286 and 80386 computers running at 12-40MHz, speed was important.

We studied numerous access technologies. One of the slowest forms of access was sequential search, where the program looked at each position in a +1-format. Searching customer 1 and 2, then 3 etc was pretty slow when you had a client database with 5000 customers. Running a search where you checked number 2500, and then found it was still too far, then 1250, and backwards to 625 etc could be useful when your database was optimized, but when it wasn't optimized, it would seem like finding a needle in a haystack...

You will read more about improvement in seaches in the subheading Pointers below.

Optimization
Optimizing databases was typically done at night time. It could be either based on customer numbers (typically the client's phone number), or related to name in an alphabetic format.

Non optimized you might have the following numbers:

11 - 29 - 16 - 42 - 66 - 22 - 99 - 1 - 6

Optimized, you would have the following numbers in sequence:

1 - 6 - 11 - 16 - 22 - 29 - 42 - 66 - 99

You get the picture.

Similarly, when you had company names, you might have the following non optimized sequence:

Johnson - Anderson - Cooper - Lybrand - Coca Cola

Optimized, this would become:

Anderson - Coca Cola - Cooper - Johnson - Lybrand

You get the picture.

Problems start to pile up when companies change phone numbers. Scalable systems require you to have a database of historic information, combined with Pointers as I will describe below. Once you know how to design your database, it can become a valuable tool, but it requires regular testing and constant backup. Your database will become the core of your business, so you need to take this very seriously.

Pointers
The idea of pointers is that you have a cutomer number with fixed information in one table, and this is then the pointer over to the order system for specific information on what the company has purchased, and pointers to delivery adresses - especially for companies that are spread all over the country. Pointers can pick specific positions in other tables, and need not be identical. You can have a company such as Coca Cola with perhaps four delivery addresses in 37 out of 50 states in the United States as one example. In other states, each bar or serving café may be a separate entity in your customer database. Pointers can help separate customer data from product data.

You will get more on databases in Lesson 2. Stay tuned.