Structuring Challenge

Need help Database
1

I am creating a database to create and publish events>> The events table include fields such as Event Name, Event Description, Event Image…etc…

The information that is needed also includes things like “Event Category” and “Event Prices”

Let us start with event category: Do I create a Event Category new table such as:
Type: event category
Field: event program name
field: event ID (from events table)

or is there a way to create the Event Category as a field within the events table as list of things> if as a list of things… what I need is either to choose from the available ones and if not I want to be able to add a new one and choose it from the input form and not by adding it the database. if so how?

“Event Prices” since this has to fields which are “Price Type” and “Price Value” meaning each price type will have a value… meaning that each event will have different types and those types are shared across all events to choose from.

Let me explain: Price type examples: member price, non-member price, child price, Early bird price for members, early bird price for non-members…etc.

Now those types are used in most of the events but not necessarily all, and some events might require new types that might not have been created before… my thinking is that I need to create a new table for prices such as:
Type: Event Prices
Field: Price Type
Field: Price Value
Field: Event ID (from event table) (here another question would be: from the event id can I see reports that connect to other Type: “Events” fields?)

and if this is the case does that mean that I need to create and save the event and then add its prices so that the price table can see the event id in question?

I would highly appreciate your help

2

Rami - I happen to have run events for a number of organizations over several years, so I understand this space well.

To start off, I think your database structure would be driven by the use cases such as:

  • will you offer a way to search for events at a given price point?
  • an event could fall into multiple categories and offer multiple price points

I would create the Category thing and add a field in the Event thing for category as a list (see attached PDF if it helps). I would do the same for Price as well - create a price field in the Event thing as a list so when a user looks up an event, they know which price categories are available. You could also show only those categories that pertain to the type of user (Student, Member, Child, etc.). I would also create an events field in the Price thing as a list so at any given time, you can tell which events are available at a particular price point (if that is a requirement).

Hope this helps in some way. All the best with your project.

event%20database
event%20database633×679 36.8 KB

2 Likes
3

This is really great… when inputting information do I need to save the event things first and then choose the event to select the category. Is there a priority in saving info in the database in this case so that table read from each other when using the input forms?

4

I think the user experience would be better if saving the info is seamless.

Ideal scenario: while editing (or adding) an event, the user can choose the category (or categories) that the event belongs to.

You can implement this in a drop down from where each choice gets added to the category list for that event.