Database Design - A Complete Study
Part 1 - The Theory

Written by Stéphane Richard (Mystikshadows)

INTRODUCTION:

Today, with FreeBasic and it's new realm of possibilities, the Qmunity can now see database application as a definite possibility. Indeed efforts are being made to integrate ODBC to FreeBasic and with it, a whole new world falls within the grasp of freebasic developers. It's not enough today to say that your program can handle 10s of thousands of records the industry is much more demanding than this. Even if you could code to handly more than this say using random access files, would you really want to knowing there's a better solution out there? I didn't think so.

In this document, I will try to tell you everything you need to know to equip yourself with a good solid knowledge about databases and database design concepts so you can sit down, look at a problem and be able to intelligently design a database to fit the very specific needs of a given database project. This will be a 3 part study. The first part, which you are reading now will cover alot of the basic database concepts, What types of database exist and some design considerations. The Second part will take a sample situation and design a database for it. The third part will cover everything there is to know about database management, interrogation and reporting. Throughout this series we will be using MySQL as the database environment because it's free and will most likely be the first "bigger" database you'll want to get your hands on. So all SQL you'll learn will be as it is implemented in MySQL. With a few changes you can addapt your knowledge to most well known databases fairly quickly.

DATABASE CONCEPTS:

We'll start things off by explaining some basic database concepts. Like I mentionned this first part is all about theory so brace yourselves. Here I'll just explain some parts of database design that you can expect to see in all "database related projects" by today's standards as they have changed over the years.

DATABASE TYPES:

Indeed, over the years there have been many types of databases developed. The main reason for the different types was simply because of the evolution of the data that were needed by the industry. It was, in my opinion a very evolutionnary (not revolutionnary) sequence of database types that simply fit the needs of the data that would be handled by the databases of the times. Here we'll cover these database types.

DATABASE NORMALIZATION:

You seen this term earlier in this document. Database normalization is the process in which duplicate information is prevented and avoided when designing a database. There are 6 defined normalization levels (5 distinct levels and an modified version of the 3 normal form). Each of these normalization phase is designed to refine the previous level a degree more to assure that the database definition is properly normalized. Let's see these Normalization Forms along with their description. After we'll take an example situation and create a normalized database to reflect the needs of that situation.

Special Note:
I've stated here all the normalization forms. However, more than likely most database designs consider only the first three normaliziation levels. The reason why is because the other normalization levels usually cost too much in performance to be useful. All these normalization phases can be applied in both a relation DBMS and an Object-Oriented DBMS, the only difference is how you imply a relationship between two tables. In a relation DBMS you simply create a relationship stating which fields are the foreign keys going into which table and if it's a one to one, one to many, etcetc type of relationship. In Object-Oriented DBMS you create a relationship in terms of what the parent table contains (An InvoiceHeader table has a InvoiceDetails Table to hold the items purchased by the customer and specify the key that holds this containment relationship together).

IN CONCLUSION TO THIS FIRST INSTALLEMENT:

And this is it for the bigger part of the theory (the first installement of this series). There's alot of material that I've covered and that's only to prepare you for what's up ahead. I tried to make it as concise and clear as I could to help make sure that the next installement will go smoothly. If you have questions, be sure to let me know so I can explain it further if needed.

In the next installement we'll take a complete sample situation and we'll define a database to represent the situation. We'll also normalize the database as per what we've learned here in terms of normalization. In other words, we'll begin to take the theory you learned here and apply it to a realworld situation. I'll give you tips and pointers to help you figure out what type of table you'd probably need as well as what fields you'd typically need to properly describe the items being stored in the table. Database design is really about domain specific knowledge and common sense. The way the situation is described to you often holds key information when you try to evaluate the database needs of a project. I'll show you how to extract that information from a typical description and you'll see it becaomes a very straightforward process in the ned. Until then, Happy coding and remember that you can always email me with questions or comments.

MystikShadows
Stéphane Richard
srichard@adaworld.com