New to databases - am I on the right track with this one?

M

Mike

Hi folks,

I'm trying to create an internal database for tracking clients. I don't
think what I want to do is very difficult, but I would like to create a
database so that we could have a different record for each interaction with a
client, and be able to create reports showing those interactions, meetings,
phone calls, services provided, etc. I work for a non-profit and we do
business and housing counseling. Here is what I have so far.
1) A table with client demographic info (client ID, name, address, contact
info, etc),
2) A table with employee info (Employee ID, name, etc),
3) A table with meeting info (meeting ID, date/time, total hours, type of
meeting, and memo notes.

I'm not exactly sure how to link all of these tables together with
relationships, and I'm not sure if I have all of the appropriate tables. Do
I create a relationship between the client table and employee table, and the
client table and meeting table, and do the same for the employee table and
meeting table? Does this sound like I am on the right track?
Also, I'm working with a access database, but I'm running SBS 2003, so I
could do this with SQL if that would seem more beneficial. Thanks for any
advice.

Mike
 
B

BruceM

Leaving aside the employee information for now, you are correct that you
will need a Client table and a Meeting table. Each client may be the
subject of several meetings, so there is a one-to-many relationship between
Client and Meeting. To accomplish that, you need a linking field, which I
will call ClientID in both tables.

tblClient
ClientID (autonumber primary key, or PK)
ClientName
Address
etc.

tblMeeting
MeetingID (PK)
ClientID (foreign key, or FK)
MeetingDate
MeetingHours
Notes
etc.

I have arbitrarily assigned ClientID as an autonumber field, but there are
other options. Autonumber is virtually guaranteed to be unique, but another
unique identifier will work. If ClientID in tblClient is autonumber, it
must be Number (Long Integer) in tblMeeting; otherwise they must both be the
same data type as established in table design view.
Go to Tools > Relationships. Add both tables. Drag ClientID from one
table, and drop it on ClientID from the other table. This does not move the
field from one table to the other, but rather it establishes a relationship.
Click Enforce Referential Integrity in the dialog that appears.
Create a form base on tblClient, and another based on tblMeeting. Select
Subform/subreport from the toolbox, and draw a rectangle (which is the
subform control) on tblClient. Set its Source Object to the name of your
Meeting form. Click the Three dots next to Link Child Fields, and select
ClientID for Child Fields and for Master Fields. These fields will probably
be the suggested ones in the dialog box.
Enter a Client record in the main form. Now you can add any number of
Meeting records for this client. Note that you do not need ClientID on the
subform, as long as it is in the subform's Record Source. The only
information about the client that you are storing in tblMeeting is ClientID.
The rest of the Client information may be linked as needed, here or in a
report or other form.
Get the feeling for how this works with just two tables. Employee
information is stored in a separate table, as you have described. It may be
added as needed to a record, but you haven't mentioned where it would
appear. I suspect it would be in the Meeting table, but if so could there
be more than one Employee per meeting?
If there could be more than one Contact per Client, use a separate Contact
table, related to tblClient in the same way as tblMeeting is related, and
set up with the same kind of form/subform arrangement.
Beware of using reserved words for field names. These include Name and
Date. For lists of reserved words:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#ReservedWords
There's a lot of information there, but Allen Browne's utility can help you
check.
 
M

Mike

BruceM,

Thanks for the detailed response. I "think" I understand all that you have
shared below. I will work on this for a while, and if I have any questions,
I will post back. Thanks, so much for your help!

Mike
 
K

Ken Sheridan

One thing you might or might not have to cater for is a meeting with more
than one client simultaneously, which would mean there is a many-to-many
relationship between Meetings and Clients. This would also apply if each
meeting is with a single client if you are logging the attendance of
individual contacts representing a client, and more than one contact might
attend a meeting on behalf of a client. The relationship in this case would
be many-to-many between Contacts and Meetings, and the relationship between
Contacts and Clients would be many-to-one. Even that might not necessarily
be true, as a 'contact' could be an agent of a client, e.g. a lawyer,
accountant etc, who might well be the agent of several clients, so again the
relationship would be many-to-many.

Many-to-many relationships are modelled by a table which includes foreign
key columns, each of which references the primary key of the related tables.
Such a table resolves a many-to-many relationship into two (or sometimes
more) one-to-many relationships.

So a ClientMeetings table (if meetings are held with multiple clients) would
have columns ClientID and MeetingID referencing the keys of the Clients and
Meetings tables.

Similarly the relationship between Employees and Meetings could be, and I'd
guess probably is, many-to-many as its likely more than one employee could
attend a meeting. Consequently a MeetingEmployees table would be needed to
model this relationship.

So if we take the following scenario as the simplest case:

1. A meeting etc will be with only one client.

2. A client's participation in a meeting etc is recorded as participation
by the client per se, not by one or more representatives of the client.

3. One or more employees might represent your organisation.

The tables, shown in square brackets, and their relationships would be as
below, with the < and > characters indicating the 'many' end of the
relationships in each case i.e. the 'referencing' table which includes a
foreign key pointing to the primary key of the 'referenced' table, and the
field names in parentheses indicating the keys on which the tables are
related:

[Clients]--(ClientID)--<[Meetings]--(MeetingID)--<[MeetingEmployees]>--(EmployeeID)--<[Employees]

If a meeting can be with multiple clients:

[Clients]--(ClientID)--<[ClientMeetings]>--(MeetingID)--[Meetings]--(MeetingID)--<[MeetingEmployees]>--(EmployeeID)--<[Employees]

If a client, whether single or multiple clients per meeting, can be
represented by multiple contacts at a meeting:

[Clients]--(ClientID)--<[Contacts]--(ContactID)--<[ContactMeetings]>--[Meetings]--(MeetingID)--<[MeetingEmployees]>--(EmployeeID)--<[Employees]

You might find the above easier to follow if you draw it rather like a flow
chart, with boxes representing the tables and arrowed lines between them
representing the relationships, what's known as an 'entity relationship
diagram'.

All tables model entity types, and their columns the attribute types of
those entity types, e.g. Clients is an entity type and FirstName, LastName,
etc are attribute types of the entity type. Its important that each
attribute type must be what's known as 'functionally dependent' solely on the
whole of the key of the table, otherwise redundancy and the possibility of
inconsistent data is introduced. For instance address data might include a
CityID column in a table, but should not include State or Country columns.
There should be a separate Cities table with CityID, City and State columns,
a States column with a State and Country column, and a Countries table with a
Country column. When a table models a relationship type, as with
MeetingEmployees above, its is nevertheless modelling an entity type as a
relation ship type is in fact just a special kind of entity type.
Consequently a table modelling a relationship type can have attributes (i.e.
columns) in addition to the foreign keys referencing the primary keys of
other tables.

Its important, therefore, when deciding on the appropriate logical model for
your database that you consider not only whether the tables accurately
reflect the entity types and relationship types of the part of the real world
which the database is intended to model, but also that the columns of each
table accurately reflect the attribute types of that entity type and no other.

Ken Sheridan
Stafford, England
 

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

Similar Threads

Creating a table and relationship for counseling notes question 2
Filtering a Lookup Field on Two Criteria 1
How do I create an attendance register for those present on a date 2
Subform with two keys 4
Converting a list of fields to a single field 2
Training Database 1
Best way to track availability in a form 0
New Database 2

Top