1 Reply Latest reply on Aug 2, 2010 8:38 AM by FentonJones

    Newbie:  Entity Relationship and Database Complexity



      Newbie:  Entity Relationship and Database Complexity


      Hello - 

      I'm very new to this whole database thing and really trying to wrap my head around some of these concepts before I dig in to build my first DB.  It's been very hard for me to apply the Entity Relationship information to my own particular model.

      I'm an Event Manager (and yes, I found the quick start template) and one of the first questions I'm trying to settle is:  one database for all events, or one database for each event?

      It seems to me that the relationship complexity factor goes way up if I try to build a DB that tracks all my events, however - it would be nice to track some things across events (like vendors and guests).  It seems to me, thought, that if I try to build a DB that tracks all events - it generates a lot of many-to-many relationships.

      My list of Entities looks something like this.






      Team/Production Staff


      My quandry is that - there are a few groups here that might be some upped as "Contacts" or "People".  It seems to me that I could incorporate these into one group and then designate the type of contact/person within that group.  I am assuming that I could setup the DB so that only particular fields are available for each type.

      Is it also possible to setup a contact data entry panel so that, based on the type of contact selected, that contact would be given a different ID type/serial?

      But then, given the relationships with other entities - I can see the value of having these contact groups as separate entities.  Vendors, for example, have a definite relationship with the budget - but guests and staff don't.

      Anyway - any guidance appreciated.  Thanks

        • 1. Re: Newbie:  Entity Relationship and Database Complexity

          Generally, databases reflect reality (:-]). There is only 1 "events" entity. There may however be more than 1 "people" entity. But only if one group of people never take part except in one specific way (or almost never and you're willing to live with the exceptions), and only if it really matters. 

          That being said, given what you've said, I think I'd split Guests, Vendors and Staff into 3 tables. Depending on complexity all 3 may be have access to tables like Notes, Calls, Address, Phones, ie., anything that "any person" would need. Normally I'd have multiple ID fields in these kind of tables, one for each (3 in this case). Alternatively you could have only 1 ID field, with different prefixes on the ID for each type (in each parent table). 

          The general idea is that each group has its own operations which do not overlap. You never do the same kind of thing with more than one group. And if you do, like a Find on a name, or sending all of them a letter, you handle that within a script.

          It also means however that each group would need its own "join" table between its parent table (Guests for example) and Events. Alternatively, you could have 1 join table, and a "role" field to see what kind of person they are. But I wouldn't.

          You may still need a "role" field in the join tables, say for the Events|Staff, who may have different roles per person, even different roles on different events. 

          As you see, there's two ways to do this. But if their operations really are separate, and unless there's a reason not to, I'd split 'em.