How would you...

D

Daniel

Hello,

I just wanted a second opinion on the proper way to structure my database.
I am wanting to try and learn a little about access and so decided to create
a contact management database. I wish to store person's name home info as
well as office info.

Am I better storing all the info in one table or split it up into three
tables (gene info, home info, business info). When do you decide to create
a new table?

Thanks,

Daniel
 
J

James Hahn

You would create a new table if
- The number of pieces of information (eg, contact events) that can be
associated with one person can vary.
- Several people can share the same information (eg, company details). It
would be inefficient to repeat these details for each person.
- You want to force the details for that person to be consistent with other
data (eg, postcodes).

You might also create a new table for any subset of the person's data that
is typically accessed (update, report) as a single logical block of data.

Very often these decisions are simple functional decisions, not dictated by
specific rules. I have seen contact databases where there are fields for
one home address and one business address. This would create problems for a
contact who works at two places. Another database might allow for any number
of addresses, so they are stored in a separate table. This is more flexible,
but more complicated.
 
V

Van T. Dinh

The general method I normally use is to idetify the different entities I
need in the database then the details for each of these entities is store in
a single Table. After that, I may need to use extra Tables for look-up or
for the Many-to-Many relationship.

For example, if you look at the sample database "NorthWind", you will see at
least 4 main Tables:

* Products
* Customers
* Orders
* Order Details

Perhaps, this is the suitable time for you to check out the Relational
Database Design Theory and the Database Normalisation technique which can
help you to decide the structure for your database.

HTH
Van T. Dinh
MVP (Access)
 
J

John Vinson

Hello,

I just wanted a second opinion on the proper way to structure my database.
I am wanting to try and learn a little about access and so decided to create
a contact management database. I wish to store person's name home info as
well as office info.

Am I better storing all the info in one table or split it up into three
tables (gene info, home info, business info). When do you decide to create
a new table?
Click to expand...

When you have a new entity, or a new relationship.

I'm not happy with EITHER of your table design suggestions. Storing
"the same" data in three different tables is emphatically NOT good
design.

The entities that I would suggest that you consider are People - a
table with the person's name, and basic biographical data as needed
for your database; and a table of Addresses. These two tables are in a
many to many relationship, since one person might have multiple
addresses (home, work, vacation home, maybe more); and one address (an
office for example) might well have multiple people. As with all many
to many relationships, you need a third table (PersonAddress) to
resolve this relationship. This table would contain the PersonID and
the AddressID but would NOT contain any biographical or address
information - perhaps just another field or two to indicate the type
of address and perhaps an effective date.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top