First db design - normalisation question

[Marie B]

Hi,

This is my first attempt at designing a db from scratch, and it might take a
while to explain so please bear with me!

I'm using Access 2003 and trying to create a contacts db for our work
project. So far in my design, I've got three tables:
tblOutsideContacts
tblOurStaff
tblMeetings (which has records for each time someone from tblOurStaff meets
with someone from tblOutsideContacts)

Each person (from either tblOutsideContacts or tblOurStaff) can be involved
with >1 Department (there are 8 depts). I want to report on:

i) a list of activity in each Dept for last two weeks, including who
attended the meetings
ii) search by Dept to find related people
iii) search for a Person and find their Dept(s)

What is the best way of recording Departments on these three tables? Do I
need a separate Department table? If so, what is the best way to link it to
the existing tables?

Apologies if I'm missing something simple, just can't get my head round it.
Hope this makes sense, all help very gratefully received!

Thanks,

MB

 

[Bernard Peek]

Hi,

This is my first attempt at designing a db from scratch, and it might take a
while to explain so please bear with me!

I'm using Access 2003 and trying to create a contacts db for our work
project. So far in my design, I've got three tables:
tblOutsideContacts
tblOurStaff
tblMeetings (which has records for each time someone from tblOurStaff meets
with someone from tblOutsideContacts)

If there is a many to many relationship between meetings and staff you
need a linking table. You probably also need a linking table between
contacts and meetings.

Linking tables contain at least the fields that are the primary key of
each of the tables they link.

Each person (from either tblOutsideContacts or tblOurStaff) can be involved
with >1 Department (there are 8 depts). I want to report on:

Just what role does the department play? Do you just need to record the
department that the staff members belong to, or is there some other
relationship between meetings and departments.

 

[Jeff Boyce]

Marie

I usually recommend that someone in your situation sit down with paper and
pencil and start drawing pictures ...

First, make a box to represent one of the "thingies" about which you want to
keep information. From your description, it sounds like Meetings is one of
those thingies. Now make other boxes to represent other
persons/places/things about which you want to keep information.

Now put the kind of information into each box that fits there. For example,
if you have an OutsideContact box, I'm guessing you'll have a LastName, a
FirstName, and maybe an Email address -- these are attributes/features of an
OutsideContact.

If you find that you have the same kinds of information in two boxes,
consider that maybe you only have ONE thingie, not two. In your description
you mention OutsideContacts and OurStaff. I'll assume that both hold
"people", and information about people (e.g., Name, contact info like email
addresses, ...). If you are keeping identical types of information (or
close) about both OutsideContacts and OurStaff, consider using a single
table that holds "people".

Whether or not a person is an OutsideContact or an OurStaff may change over
time. In your situation, could you have someone who used to work for you
leave the company and become an OutsideContact? ... or vice versa?

Relational design and normalization are part art, part science. There's no
one right way (OK, so some of the gurus insist that there is, but this is
the real world!).

Good luck! Feel free to post back with further questions, or come back and
answer some you know the answer to!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marie B]

Hi Bernard,

Thanks for the info - I'll look into linking tables.

Much obliged.

Marie

 

[Marie B]

Hi Jeff,

Many thanks for your informative and welcoming reply - will take your advice
and think about whether I need two tables for contacts, or can put them into
one...I may have been a little over-enthusiastic on normalisation after
reading a couple of articles!

No doubt I'll be back on these boards soon enough

Regards,

Anna

 

[Jeff Boyce]

Marie

That's why folks hang out here...!

Regards

Jeff Boyce
Microsoft Office/Access MVP