Basic Design Question - Part Deux

[bigal.nz]

Hello all,



We have a flat table of events (table_events) containing dates, times, people, and comments (among other fields) - 10's of events are entered into the database daily.

Once / day there is a meeting where events are discussed by several people from one of four different companies. During the meeting a form displays the details of each event.

The people at the meeting change, but the companies present is static.

I need to record who was present at each meeting.

The question is how to do this?

Based on my DB reading so far I would:

Create a table table_meeting

Fields:

Event ID, PersonID, Date_of_Meeting

Create a table_people

PersonID, First Name, Surname, Company

Assuming the above is correct, I am a little fuzzy on whether I need to link the tables?

When I open the form that opens the record set at the beginning of the meeting how would I record the attendees? Another form? Two boxes where you drag the list of potential people to the actual ones attending?

Cheers

-Al

[John_sc]

Your Table design is not correct. it is not normalized, which means that your table contains fields that are not related to a single Object. The Event ID and Date of meeting deal with the meeting, but the person ID deals with the attendees. If you had 10 attendees, you would have to insert the date of the meeting 10 times. This is not considered a good design.

You should have separate tables to describe the objects that you are tracking. These objects are typically Nouns - in your case the Meeting and the People. Then you need another table which will link these two tables together. Put everything related to one object into its associated table. So things that deal with the Meeting go into the meeting table. This might be the date and time, location, topic, Minutes, etc. Notice each of these relate to a specific meeting and are entered only once for each meeting. Put things that deal with the Individuals in the People table, such as Name, phone number, title, company name, etc. Again, each of these properties relate to the person and is entered once for each person. For each table you will need a primary key, such as MeetingID and PersonID. Note, If the comments field relates to the comments made by an individual at a meeting, you would not put the comments in either the person or the meeting table, since the comments relate to both (what the person said at a particular meeting).

You will need a third table which will tie the two other tables together. This will use a compound key consisting of both the MeetingID and PersonID. This will allow you to tie all the tables together. In addition you would include fields that simultaneously relate to both the meeting and person, such as comments, time arrived, etc.

If the meeting has a known list of individuals attending, then this list could be preentered prior to the event and the MeetingAddendee table could be preentered, and then you just need to mark if the individual attended or not with a boolean field. If it is now known who will attend ahead of time, you could use a combobox which allows the user to select from individuals in the Person Table. you could use a subform to enter attendees.

Note, you will likely run into something called referential integrity constraints when you someone attends the meeting that is not in the Person list. This is actually a good thing, since it helps improve the quality of the information in the database. You may first need to enter the individual's information into the person list before they can be listed as attending the meeting. This will also address the problem of having two different individuals with the same name.

[bigal.nz]

Right so let me check something - the third table that ties them together will have a entry for each person at the meeting, ie:

mtg_id, person_id
001, 002
001, 007
001, 005

So in the above example it records that three people (2,7 and 5) were at meeting 1.

Should the field names in the table that ties them together have the same names (person_id etc) as the corresponding fields in the person table etc?

Cheers

-Al

[John_sc]

It is not required, but it does make it easier to understand the database design if they do have the same name.

[bigal.nz]

And what ties each event to a meeting?

[John_sc]

According to your original post, the meeting table has an EventID. Basically event and meeting are being used interchangeably (they are the same thing)

[bigal.nz]

Right, that would mean there would be a entry on the meeting table for every event on tbl_events - wouldnt it be better to add a field to the events table - mtg_id - that records what meeting each event was dealt with at?

[John_sc]

It all depends if you have multiple meeting at each event. If you have a big sales event, and have 5 breakout meetings, then you would want an Event table and a Meeting table. Going back to what I originally said, Meetings and events are both Nouns, and they are both different. So the Event might have an EventID, EventTitle, EventStartDate, EventEndDate, etc. while the Meeting table would have EventID, MtgID, Room, Topic, StartTime, etc. Then the attendanceTable would have EventID, MtgID, PersonID, comment, etc.

You should avoid having spaces in the field & table names, and you also need to set the primary keys appropriately.

[bigal.nz]

Hi John,

No there is only one meeting for each event.

So record which meetings deals with which event on tbl_events?

Cheers

-Al

[John_sc]

This is a design thing. If a meeting has it own properties, you might want to have a separate table. However if there is only ever going to be one meeting per event, the meeting properties could be included with the event properties, and there would be no need for a separate MeetingID. The EventID uniquely would identify the Meeting.

[bigal.nz]

This is a design thing. If a meeting has it own properties, you might want to have a separate table. However if there is only ever going to be one meeting per event, the meeting properties could be included with the event properties, and there would be no need for a separate MeetingID. The EventID uniquely would identify the Meeting.

Ok. One meeting can deal with many events, so I think I will record it on table events. Cheers