Structure Ideas

[dawnd3]

Hi everyone, I am creating a database for someone with a very small budget. Plus I really am not that into creating databases anymore, since I am not that high level, but she is a long-time client so I said I would. Anyway, the database is to track events for a city organization. All of the events have different information that needs to be tracked so it doesn't make sense to create just one form for all the events to share. But I don't know that I want to make different forms for each event, especially since they might add events in the future and I want them to be able to do that without calling me. So, I am just looking for idea of how I can keep this simple and easy (even if it is a tad un-normalized). I thought that maybe I could turn fields on and off depending on the event by having them set up the event and checking which fields they want, but I have no clue how to make that happen. Thanks in advance for any tips, ideas, feedback, etc.

Dawn

 

[MichaelRed]

look into the 'structure' of MS Outlook. Another excellent example of the more generic / ad-hoc approach is the registry.

Neither is necessarily 'easy' to wrap in the traditional database concept, but offer a great deal of flexibility in the storage and presentation of information.



MichaelRed

 

[dawnd3]

Michael I can envision how outlook type structure would help me with tagging contacts into various categories, but can't picture how it will help me with the event entry set up. I don't know what you mean by the "Another excellent example of the more generic / ad-hoc approach is the registry." Again, I can't quite figure out how to set up the events so that they can all track different pieces of information. I would just farm this out but they aren't paying me very much!

 

[jsteph]

dawnd3,
There's nothing wrong structure-wise with having a "Master" events table and multiple events tables--one for each type of event. This would be a one-to-one relationship;

A better way to envision it is to start with--lets say you have 5 event types--5 event tables each with varied sets of fields. Have the key for each of these tables be two fields--an event-type and a sequence number. Then you have a "master" event table that would hold information fields common to all events--keyed on event-type and the number.

The difficulty is in creating the forms--there's no easy way around that. I've seen it done with a single tble with a huge number of fields and bizarre code in the form to turn fields on and off--it's a real mess. At least with the multiple tables approach you might be able to get by with the form wizard, then hack in the join to the "master" table and maybe have a one-to-one subform for the master at the header of each of the event forms.

Bottom line--if each event is fundamentally different in the data and structure, then you're not breaking rules by doing the 5 (or whatever) tables. It's just that I cringe when I think of setting the .visible or .enabled property of bit swaths of fields based on an ever changing and increasing number of variables (event types).
--Jim

 

[MajP]

You need to provide more information. But my guess is you are wrong about them tracking different kind of information for events. Just naturally a buisness or agency will be concerned about the same type of information. My guess dates and schedule, personnel, costs, activities milestones, etc. It is basic program management.



Just a guess

tblEvent
eventID
eventName
eventLocation
eventDescription
eventStart
eventEnd
other common fields for all events

tblEventDates
eventID_fk
eventDateName
eventDateDescription
eventDateStart
eventDateEnd
other generic date/milestone fields

tblEventCosts
eventID_fk
eventCostType
eventCostName
eventCostDescription
eventCostValue

tblEventRequiredActivites
eventID_fk
eventActivityType
eventAcitvityName
eventActivityDescription
eventActivityStartDate
eventActivityEndDate
eventAcitivityDueDate
eventActivityIsComplete
other fields unique to activities required to be accomplished.

tblEventPersonnel
eventID_fk
eventPersonnelName fields
eventPersonnelRole

etc.
.....

 

[dhookom]

I agree with MajP. I might add tables for activity type and cost type.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

Again, I refer to the Outlook model. One element could be the flags which allow one to designate a specific as one of a variety of categories. I the more traditional Forms this could easily be transformed as either an option group (relatiely static elements) or a combobox with the possability of adding categoriec in an ad-hoc manner.



MichaelRed

 

[MrDataGuy]

Small Budget, Lots of flexibility, runs with no or very little maintance/support, able to handle unknown future needs, Government Agency. Why not ask for the world?

"I said I would"

I think you may have bit off more then you should/can provide unless you are ready to donate a significant amount of time.

Best I can suggest is to limit the flexibility and have a limited number of commen event fields: Event Name, Date, Primary Contact etc. Then allow for the use of notes. Maybe several notes that they can use as they need. Throw in full text search on those notes fields.

Also might a simple Excel Spread sheet be a possible solution?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[dawnd3]

Hi everyone, thanks for your feedback. It turns out about halfway through this thread that I realized that it isn't the event info that is different, it is the info that is tracked for each contact depending on the event. So my thought was that I would create "event types" and then filter the contacts that are participating in the event, based on the event type. So my question now is, how do I do that? So if I am looking at an event entry form, and I have a button that opens the form to select the contacts that will participate, how do I get that form (in continuous forms setting, I think) to show just the fields that the event type requires? Can columns be filtered out?

Thanks for your help on this!

Dawn

 

[MrDataGuy]

Are you familiar with software design patterns? I think this begs for the Factory Pattern. Now a pattern does not tell you the specifics on how to write the program, but will get your brain thinking in the right way.

Example:

http://en.wikipedia.org/wiki/Factory_method_pattern

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[dawnd3]

Have not heard of that. I am not a very high level programmer. I will take a look. I have a brainy husband if I need help understanding it conceptually.

 

[MichaelRed]

perhaps " ... being tracked ... " could be explained? If it is just 'dates" (of schedule / occurance?). A linked table with the Event, Info and Date (scheduled / occured).

Otherwise what tracking is required?



MichaelRed