The Data Model

Discussion Datamodel for 2007

COLOR CODING:

• Yellow - Person related
• Blue - Inventory related
• Red - Event-Organization related
• Purple - Budget Related
• Blue - Inventory related
• Green - Application related

MAJOR ENTITY DESCRIPTIONS:

• Person - basic information about a person (name, DOB, gender)
• Event - identifies events including the timeframe during which they start and stop
• Organization - identifies various organizations, for example, "Tofu Palace", "Mud Bay Jugglers", "BUM-Grumpy", "Traffic"
• Event_Organization - Identifies all of the Organizations which are participating in a specific Event. Most roll forward from the previous year. Anticipated volatility is with Entertainment organizations
• Organization_Types - Identifies the kinds of organizations which participate in an Event. For example: Vendor, Crew, Admin, Entertainment, CVillage, EPark
• Inventory_Class - defines high-level "classes" of inventory such as "Tshirt", "Wristband", "Vehicle Sticker"
• Inventory_Type - identifies specific inventory items with an optional class qualifier. ("XL Staff TShirt", "Emergency Vehicle Sticker"...)
• Affiliation - This is a central table that identifies who has attended an event and in what capacity.
• Affiliiation_Role - Identifies the roles a Person may have during an Event. For example: Vendor, Booth Rep, Crew Member, Crew Coordinator, BUM, GM,...
• Affiliiation_Type_Inventory - (pending renaming to Affiliiation_Role_Inventory) Identifies the types of inventory a role is elligible to receive. For example a Booth Rep can get Worker Day Passes, but not Staff Tshirts.

HOW THE DATAMODEL WORKS

Inventory classes ("Tshirt", "Wristband", "Vehicle Sticker") are created and from them specific Inventory Types identified ("XL Staff TShirt", "Staff WB", "Service VS"). Each type of organization (Organization_Type) has different types of inventory (Organization_Inventory_Type), and this may change per Event (Event_Org_Inventory). The Budget Process associates specific event inventory (Event_Org_Inventory) with organizations (Event_Organization_Budget_Item); only inventory appropriate for the type of organization is permitted for budgeting. For example, "Tofu Palace" would not be able to get "Food Voucher" inventory included in their budget.

Organization leaders (Booth Reps, Coordinators, ...) will manage who is associated with their organization by modifying Affiliation records. Jane Doe may have, for example, multiple affiliations for a single event: she may be associated with a vendor, may also be an enteretainer, and could possible be a crew member too. In this example, Jane would have three different Affiliation records for the specific Event. Only the booth rep could modifiy Janes affiliation with that bnooth, they could not touch Janes other affiliations. After the budget process is complete, the organizaion "leaders" can assign budgeted inventory for thier org (Event_Organization_Budget_Item) to specific individuals (Affiliation) in their organization. This assignment of inventory to individuals is recorded in Affiliation_Inventory_Allocation.

Note that, for each Event, some of the specific Inventory prices may change. We need to determine whether this is also sensitive to Organization..I believe it is.

Note: Crows feet with a single cross-hatch indicate that the primary key (PK) from the source table becomes part of the target table PK.

JohnChambers

John's version of Jon's Db.

Here is my first attempt to put Jon's data model together.

Note the complications are:

1. no constraints are defined in the database (at least from the scripts I have) so I'm only guessing about foreign key constraints
2. his tables all use UniqueID as primary keys, so again it's not clear how to make the joins

However..in my diagram I did color all of the "person-related" stuff yellow and "Inventory-related" stuff a rust color. I did represent the joins to Person, but not on Inventory.

Notably absent are "Organization" and "Event" tables....I guess I need to review his code as to where he was getting this data.

Happy TDay everyone...I'm now off to my sweeties for dinner.

JohnChambers - 23 Nov 2006

The DGP version of the Data Model

Well this isn't a half bad rendering of Jon's data model.

I found a perl script to translate between sql and other things like the dot language. The project is called http://sqlfairy.sourceforge.net/ and here is the man page for sqlt-graph.

Here is a recent copy of test.sql. The Indexes, and FOREIGN KEYs I added may not be useable in their present form. I don't quite Grok the Allocation and Item tables, does someone know how they are used? Maybe Settings, and Budget are the "Event", and "Organization" tables John was missing below?

My command line looked like this:

sqlt-graph --from=MySQL -t canon -o out --no-fields ../test.sql

Then I pasted the dot language output, (for DirectedGraphPlugin) into this page, but I couldn't resist coloring it by hand so I got out my crayons... like so:

digraph test { graph [ratio=fill, overlap=false]; node [label="\N", fillcolor=yellow, shape=ellipse, style="filled"]; access [label=access,fillcolor=gold1]; address [label=address]; allocation [label=allocation,fillcolor=coral1]; budget [label=budget,fillcolor=coral1]; days [label=days]; email [label=email]; hours [label=hours,fillcolor=coral1]; inventory [label=inventory,fillcolor=coral1]; inventory_type [label=inventory_type,fillcolor=coral1]; item [label=item,fillcolor=coral1]; message [label=message]; password [label=password]; person [label=person]; phone [label=phone]; preferences [label=preferences]; purpose [label=purpose,fillcolor=coral1]; received [label=received,fillcolor=gold1]; role [label=role,fillcolor=gold1]; session [label=session]; settings [label=settings,fillcolor=coral1]; budget -> access; budget -> allocation; budget -> hours; budget -> item; budget -> received; budget -> role; inventory -> inventory_type; inventory -> purpose; person -> access; person -> address; person -> days; person -> email; person -> message; person -> password; person -> phone; person -> preferences; person -> received; person -> role; person -> session; purpose -> allocation; purpose -> item; purpose -> received; settings -> budget; settings -> inventory; } -- ClifCox - 27 Nov 2006