Event tracking design question

[Tom via AccessMonster.com]

Hi,
I am tring to design a database that allows me to track an event that occurs
each year. The event only happens once a year. Several venues (separate
locations) participate in the event. Venues have some information (address)
that will not change from year to year. Each venue that participates will
lease space to the show promoter. Each show will have a name and a contact
person. Each venue will have a contact person. Each show will have some
information that will change from year to year.

I need to track information and compair information from year to year.

So far I think I need at least three tables. tblYear, tblEvent, tblVenue. Im
having trouble setting the relationships. An event can occur on several years.
One year can have several events. An event can occur at several venues. One
venue can host several events.

Would it be correct to make two many to many relationships using tblEventYear
and tblEventVenue as linking tables? Should the linking tables contain any
information besides the keys?

When I enter information into the database some information will not change
from year to year (venue address) and I would like to not enter it every year.


Thanks for the help

Tom

 

[Guest]

I am tring to design a database that allows me to track an event that occurs

each year. The event only happens once a year. Several venues (separate
locations) participate in the event. Venues have some information (address)
that will not change from year to year. Each venue that participates will
lease space to the show promoter. Each show will have a name and a contact
person. Each venue will have a contact person. Each show will have some
information that will change from year to year.

I need to track information and compair information from year to year.

I would suggest using four tables:

tbl_Events
EventID (PK)
EventInfo (various fields that describe an event)

tbl_Venues
VenueID (PK)
VenueInfo (various fields that describe a venue)

tbl_Contacts
ContactID (PK)
ContactInfo (various fields that describe a contact)

tbl_EventVenues
EventVenueID (PK)
VenueID (FK)
EventID (FK)
ContactID (FK)
EventVenueInfo (various fields that describe the specific event and venue,
which could include the date (whole date, not just the year))

This will keep all of your specific events in their own record, which will
allow you to track and compare information from year to year. Using a date,
rather than a year, in the EventVenue table will allow you to have any
combinations of event and venue during a year, but keep them as separate
records.

 

[tomanddani via AccessMonster.com]

Thanks for your reply,
I have a question. I was told that you shouldn't store information in a
linking table except keys, Is this true?

Tom

I am tring to design a database that allows me to track an event that occurs
each year. The event only happens once a year. Several venues (separate

[quoted text clipped - 5 lines]

I need to track information and compair information from year to year.

I would suggest using four tables:

tbl_Events
EventID (PK)
EventInfo (various fields that describe an event)

tbl_Venues
VenueID (PK)
VenueInfo (various fields that describe a venue)

tbl_Contacts
ContactID (PK)
ContactInfo (various fields that describe a contact)

tbl_EventVenues
EventVenueID (PK)
VenueID (FK)
EventID (FK)
ContactID (FK)
EventVenueInfo (various fields that describe the specific event and venue,
which could include the date (whole date, not just the year))

This will keep all of your specific events in their own record, which will
allow you to track and compare information from year to year. Using a date,
rather than a year, in the EventVenue table will allow you to have any
combinations of event and venue during a year, but keep them as separate
records.

 

[Guest]

I have a question. I was told that you shouldn't store information in a

linking table except keys, Is this true?

I prefer to call what I put into tables Data, and what I compile into forms
and reports Information. But that is partly just semantics. You certainly
should not store any calculated information in any table.

More to the point, in the book Database Design for Mere Mortals, Hernandez
states that "The linking table help to keep redundant data to an absolute
minimum." He then goes on to describe an OrderDetails linking table, which
links Orders and Products tables, and includes (other than the foreign keys),
the fields of Quantity Ordered and Quote Price. A linking table is exactly
where you would place any fields that refer to the record which is created by
the linking table.

In your database, a particular event that occurs in a particular venue, with
a particular contact person, would probably require a date to specify when
this unique occurrence is happening. That date cannot go anywhere else,
because it has no meaning except in the context of that particular record in
that table. It would have no meaning in any other table.

Again quoting Hernandez, "When you establish a many-to-many relationship
between a pair of tables, make certain you check each table and determine
whether there are any fields that you should transfer to the linking table."

 

[Tom via AccessMonster.com]

Thanks for your help. I've read that book, it was good, but man its hard to
put that much work into a database before turning on the computer. I know
it's the best way to do it, but its hard.

Thanks for your help

Tom