Designing a Database - First Principles

This week looks at some design concepts when constructing relational databases. It's important to address this, as a badly designed database will be slow - especially when loaded with data. It is much easier to make changes at the design phase rather than when the database is fully loaded with data.

When designing a relational database using an application such as Microsoft Access, the following points should be considered.

· Reduce duplicate data to a minimum

· What are the logical groups of data (tables) going to be?

· What columns make up the primary keys of each table?

· What foreign keys will be needed? (To relate table to one another)

Now each of these in more detail...

Duplicate Data

This is the real advantage of a relational database - the ability to cut out duplicate data to the minimum. In fact, the only data that duplicates within a relational database should be the keys - nothing else. This not only reduces the amount of data stored but also makes it easier to maintain the data.

This leads on to cascading updates/deletes and referential integrity.

Cascading updates and deletes are the ability of the database package to delete related rows stored in other tables when a main record is removed. For example, suppose we have 3 tables - A, B and C. Data is only allowed in table C if there is a corresponding record in table B. Table B records can only be present if a related entry in table A is present.

Delete a record in table A. What should happen to those related records in tables B and C? Normally, they should be deleted or, if the record in table A is updated, the corresponding records in tables B and C should also be updated. Cascading deletes/updates will take care of this automatically for you.

How can you enforce the rules that a record on C must have a corresponding record on B? This is what is known as referential integrity. This refers to the set of "rules" that you dictate when creating the database and is determined in the design phase of the database. You might wonder why you should "bother" - after all - computers these days have plenty of disk space, but having "half a record" in your database can cause problems - especially when running reports on tables that have these incomplete records. If you are going to enter the data manually - via the keyboard, then always turn on and enforce referential integrity. If you are going to load the data from another source, you may need to "clean" the data first.

Go To Page: 1 2 3

Articles in this Topic    Discussions in this Topic