How are Fields with No Data entered treated by Access ?

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

Are there any issues I should be concerened with for fields that are
provided in a table but not used on some or many records?

- Does it slow down queries?
- Take more memory?
- Eat up more disk space?
- Create larger file size ?

One reason for the question is that each Person in our data base may have...
- 1 to 5 addresses or more
- several phone numbers
- more than one email address
- etc.

We can just create a standard record... each with fields for a single
address, email and 2 or 3 phone numbers... then create additional records
for those people with more addresses, emails or phone numbers...

Or we can create a seperate table for addresses, phones & emails & link them
to the persons record... So using sub_forms we can show the person and all
their addresses, phone numbers, emails, etc.

If there are no significant trade-offs for unused fields the first method
becomes simpler... maybe.

Thanks for any comments on this.
 
You should definitely not have multiple columns in one table to represent
multiple attributes such as addresses, phone numbers etc. It is very bad
design and will cause you a lot of problems in querying the database. The
fundamental principle of the database relational model is that each real
world entity is represented by a table (strictly speaking by a 'relation' in
the theoretical model) with columns representing the attributes specific to
that entity. By relating the tables redundancy is avoided.

Bear in mind that, as well as a one-to-many relationship between two tables,
a relationship bet might be many-to-many, e.g. if two people share the same
address as well as one person having multiple addresses. A many-to-many
relationship is represented by a table with two foreign keys referencing the
primary keys of the two tables modelling the entities between which the
many-to-many relationship exists.
 
Will said:
Are there any issues I should be concerened with for fields that are
provided in a table but not used on some or many records?
Click to expand...

There are times you would want to have a field that may not be used by
many of the records. However the example you chose clearly is not one of
those times.

This may be the hardest idea to understand when first getting into
database. Almost everyone has trouble with it. Databases are not just
fancy spreadsheets, but they are (well most are) relational databases.

Access stores data in tables. Tables can be related to one another.

In this case you would want one table with the people listed and a
second table for addresses. That way each person could have any number of
address or non at all. You might want three fields in the primary database
for telephone numbers (home work and cell) or you might want another table
with just telephone numbers. The first is easy and will fit most
situations, but what happens when someone has a vacation home or a couple of
business numbers etc. No problem with a separate table.

The process of dividing up this information into appropriate tables is
call normalization (Access has information under that name in the help file)
and is as much an art as it is a science.

Good Luck
 
Back
Top