Table Design Question

D

Dee

I need to design a database that will hold contact information about our
investigator sites, contacts we meet at conferences, contact information
about our advisory boards. If the contact is an investigator site, there will
be mulitple people whose names will need to be entered into the database.
Also, if the contact is an investigator, they will have a site number and
also other info that will not be relevant to the other types of contacts (ie
conference contacts, advisory board members) and there will be infor
regarding conference contacts that will not be relevant to the others)
Because there will be more than one contact at the sites I will need to have
a address table and a personnel table, with the personnel being a subform on
a data entry form. The information regarding the sites that will not pertain
to the other contacts, (ie site number, primary CRA, Principal Investigator)
should these fields also go into the address table and just be left blank if
the contact type is something other than a site? I would probaly have a
lookup table for the contact type ( ie Investigator, Advisory Board Member)

So I will have a table called "contacts" which will be the organizations
name, I will have a table called "personnel" which will hold the name of the
contact, phone, fax etc. The info that is type of contact specific, ie for
sites it would be site id, CRA, PI etc, and for conference ie name of
confernce, date of conference, advisory board field such as "specialty",
what table should these field be in. Should I have another table call
"contact details"

I'm not sure I am making myself clear. I hope you understand what I'm
saying. Because depending on the type of contact, the information I need to
enter will be different. The problem I am having is how to set up my table to
accommodate this.
I am using Access 2003.

Thanks in advance for any help.

Best regards,

Dee
 
M

mscertified

Any time you have an object that has multiple 'types', you need to decide
whether to have separate tables for each type or to combine into one table
and have redundant columns. Ideally its best to have multiple tables, each
table describing a single type of object. It makes data retrieval much easier.

Dorian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top