|
When using database engines such as Microsoft®
Jet®
or Microsoft® SQL
Server®, remember
that these are relational database engines and your data should be structured
accordingly.
Many databases begin as a text documents or
spreadsheets. When multiple tables are involved, much of the data from the main
table has to be copied to the child tables so your users can match the child
records to the parent records and so they don't have to combine data from
several tables to create a complete record. With relational databases, you only
have to copy the primary key information from the parent table into the child
table(s), as a forign key value. If you need data from multiple tables you can
use join operations within your queries to make the data look like it came from
a single table. In an ideal situation every table would be normalized.
Normalization refers to building tables in a
parent/child relationship where the only fields that are duplicated are the key
fields. Normalization prevents data from falling out of sync because it is
stored in only one place within the database.
There are times when it makes sense to duplicate
data from the parent table into the child table. The reasons are usually
performance related. However, if you find the need to do this, you should also
program in procedures to keep the data between the tables in sync.
|