Recommendation for Lookup Table(s)

  • Thread starter Thread starter pv
  • Start date Start date
P

pv

I am tracking Events and Event Participants with three tables:

People
PeopleID (PK)
FirstName
LastName
etc.

Events (lookup table with combine PK -EventName & EventStartDate*)
The Events can either be an actual physical event i.e. an Auction or
non-physical event (i.e. fundrasing event) Annual Fund 1/1/2005 -
12/31/2005.
EventName
EventStartDate
EventEndDate
EventDescription

EventParticipants: (Intersection Table with Combined PK
EventName,EventStartDate, PeopleID)
PeopleID
EventName(FK)
EventDate(FK)
NoShow(yes/no)
ReferralFrom - field to source Event Participant to Other People in the db.
GuestOF - field to source Event Participant to Other People in the db.
Notes

What I wanted in the for event participation data entry is that the user
select the specific event from combobox based on the event name and date.
The event name and date would be stored based on this selection. All pretty
standard. So here is my question:

Many of the events occur annually and will have the same name i.e. Annual
Fund so will have many entries i.e. Annual Fund 1/1/2005-12/31/2005, Annual
Fund 1/1/2006 -1/1/2007
One Fundraising Event the Capital Campaign does not have a specific date
range which then by rules should not be stored in the Event Lookup table as
set up.
So I am looking at setting up two tables
Events:
EventName(PK)
EventDescription

EventDates: Combined PK EventName and EventStartDate
EventName(FK)
EventStartDate
EventEndDate

This would allow entry of Events without specific date range i.e. the
Capital Campaign and truly unique one time entries for re-occurring events.
Then in the EventParticipation form I would join source the combobox on the
two tables. Will setting the lookup tables this way cause any performance
issues. The db is set up on network with 10 users. This seems like I an
taking the rules of normalization to the fullest where they might be relaxed
a bit by creating an auto number for the EventId and setting unique indexing
on the EventName and EventStartDate and set field validation to required
except for the one Capital Campaign Event . (BTW - client would like to use
intelligent primary key - and I had originally thought to concatenate
EventName and StartDate to create PK and opted for combined key instead).

Recommendations welcome.
Thank you
 
Many of the events occur annually and will have the same name i.e. Annual
Fund so will have many entries i.e. Annual Fund 1/1/2005-12/31/2005, Annual
Fund 1/1/2006 -1/1/2007
One Fundraising Event the Capital Campaign does not have a specific date
range which then by rules should not be stored in the Event Lookup table as
set up.
So I am looking at setting up two tables
Events:
EventName(PK)
EventDescription

EventDates: Combined PK EventName and EventStartDate
EventName(FK)
EventStartDate
EventEndDate

This would allow entry of Events without specific date range i.e. the
Capital Campaign and truly unique one time entries for re-occurring events.

BTW - client would like to use
intelligent primary key - and I had originally thought to concatenate
EventName and StartDate to create PK
Click to expand...

Sounds to me that different classes of events have different
attributes and business rules: Annual Fund can have multiple periods
(i.e. event name is not unique) of exactly one year with no overlaps
(i.e. a more subtle definition of uniqueness called a 'sequenced
primary key'); other events may be one-off (i.e. event name is
unique); Capital Campaign has no associated dates. I'd handle this
using a 'subclassing' design pattern to allow specialist tables to
have dedicated attributes and constraints e.g. (ANSI-92 Query Mode
syntax):

CREATE TABLE Events
(
event_name VARCHAR(30) NOT NULL UNIQUE,
event_type VARCHAR(12) NOT NULL,
CONSTRAINT event_type__values
CHECK (event_type IN ('Continuous', 'Annual', 'One-off')),
UNIQUE (event_type, event_name)
)
;
CREATE TABLE AnnualEventsDates
(
event_name VARCHAR(30) NOT NULL,
event_type VARCHAR(12) NOT NULL,
CONSTRAINT annual_event_type__value
CHECK (event_type = 'Annual'),
FOREIGN KEY (event_type, event_name)
REFERENCES Events (event_type, event_name),
start_date DATETIME NOT NULL,
UNIQUE (event_name, start_date),
end_date DATETIME NOT NULL,
CONSTRAINT AnnualEventsDates__date_order
CHECK (start_date < end_date),
CONSTRAINT AnnualEventsDates__period_measure
CHECK (end_date = DATEADD('S', -1, DATEADD('YYYY', 1,
start_date))),
CONSTRAINT AnnualEventsDates__no_overlaps
CHECK (NOT EXISTS (
SELECT D1.event_name, C1.dt
FROM Calendar AS C1
INNER JOIN AnnualEventsDates AS D1
ON (C1.dt BETWEEN D1.start_date AND D1.end_date)
GROUP BY D1.event_name, C1.dt
HAVING COUNT(*) > 1))
)
;
CREATE TABLE OneOffEventsDates
(
event_name VARCHAR(30) NOT NULL UNIQUE,
event_type VARCHAR(12) NOT NULL,
CONSTRAINT oneoff_event_type__value
CHECK (event_type = 'One-off'),
FOREIGN KEY (event_type, event_name)
REFERENCES Events (event_type, event_name),
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CONSTRAINT OneOffEventsDates__date_order
CHECK (start_date < end_date),
CONSTRAINT OneOffEventsDates__no_overlaps
CHECK (NOT EXISTS (
SELECT D1.event_name, C1.dt
FROM Calendar AS C1
INNER JOIN OneOffEventsDates AS D1
ON (C1.dt BETWEEN D1.start_date AND D1.end_date)
GROUP BY D1.event_name, C1.dt
HAVING COUNT(*) > 1))
)
;
INSERT INTO Events (event_name, event_type)
VALUES ('Capital Campaign', 'Continuous')
;
INSERT INTO Events (event_name, event_type)
VALUES ('Annual Fund', 'Annual')
;
INSERT INTO Events (event_name, event_type)
VALUES ('Timetraveller Conference', 'One-off')
;
INSERT INTO AnnualEventsDates (event_name, event_type, start_date,
end_date)
VALUES ('Annual Fund', 'Annual', #2005-01-01 00:00:00#, #2005-12-31
23:59:59#)
;
INSERT INTO AnnualEventsDates (event_name, event_type, start_date,
end_date)
VALUES ('Annual Fund', 'Annual', #2006-01-01 00:00:00#, #2006-12-31
23:59:59#)
;
INSERT INTO OneOffEventsDates (event_name, event_type, start_date,
end_date)
VALUES ('Timetraveller Conference', 'One-off', #2099-12-31
09:30:00#, #2099-12-31 16:30:00#)
;

Of course, there is potential for further subclassing e.g. reoccurring
events (of which 'annual event' is a subclass), fixed length events
(two day event, all night party, etc) and so on.

Jamie.

--
 
Wow, I new of subclassing, but you have opened my eyes with using it for
this circumstance. Thank you. It has gotten me to think even more about
the events and the specifics wanted.
I have also realized that there are other events (not mentioned in first
message) that are one off events that occur of a range of time; however, the
date of these events are irrelevant in this application what is relevant is
the date that the individual attended which is record in the intersection
table between people and dates in a StatusDate field. So these would not be
subclassed - simply entered in the superclass.

I had included Event Start Date since most events occur on a single date but
then added end date because of events that do indeed have a range which
would force the user to enter end date for those events that are fixed
single date events. So this is solved by the sub class "SingleDateEvents".
In most cases the events occur once a year and all that is wanted is to
track an event by the event year including the annual fund so in the case of
the Annual Fund the event year would be 2005. There are some events however
that do occur more than once a year so the specific date (single day events)
would be needed.
And finally there are events that do occur over a range of days
So in addition to was thinking of subclassing as:

Events:
EventName
EventType

YearlyEvents: (All Events to be tracked be year they occur)
EventName
EventType
EventYear

SingleDateEvents:
EventName
EventType
EventDate

FixedDateEvents:
EventName
EventType
EventStartDate
EventEndDate


Needless to say I would have to use text field for event year. So wondering
if I am going to shoot myself in the foot by mixing the two data types for
the event date, or by not tracking start date and end date for all events.

I hope I am explaining this clearly.

Thank you again for your assistance!
 
Oops - one more questions regarding subclassing

One of the events i.e. event name e3 occurs 3 times a year.
In the table that this is subclassed in it will would the event date as part
of the primary key?

Thank you
 
Back
Top