Need help with Registration

Databases
1

Okay, so r937 helped me figured out how to model my Events, but now I need even more help with the Registration process.

This should be simple, but the more I think about it, the more confusing it gets… :frowning:

Candidate Entities:

  • Customer (person paying for Tickets)
  • Order
  • Order Details
  • Tickets
  • Attendees
  • Event

Description:
A “Customer” goes to my website, finds an “Event” that he/she is interested in, and then registers for the “Event”. When the “Customer” places an “Order”, he/she is buying generic “Tickets” for the “Event” (e.g. 6 tickets to the Mannheim Steamroller Christmas Spectacular). There is no registration - and thus no name or ID - associated with each “Attendee” other than a headcount, I suppose?!

Possible Relationships:

  • One CUSTOMER has one or more ORDERS

  • One ORDER has one or more ORDER_DETAILS

  • One CUSTOMER has one or more REGISTRATIONS

  • One REGISTRATION has one or more TICKETS

  • One REGISTRATION has one or more ATTENDEES

  • One TICKET has one ATTENDEE ??

  • One EVENT has one or more ATTENDEES

  • One EVENT has one or more TICKETS

  • One TICKET has one EVENT

and so on…

Honestly, I’m getting pretty mixed up which Entities I should use and their relationships?! :frowning:

Sincerely,

Debbie

2

what’s the difference between registering for an event and buying tickets for an event? not in database terms, in real-world terms

why are you tracking attendees if you sell tickets? who cares if the punter attends once the ticket has been bought?

3

If you are not concerned with tracking a single customers orders over time than a customers table is not really necessary. You can place the customers details inside the orders table, one row per order.

* Professionally, I don’t think it’s very smart to not keep track of a single (unique) customers orders over time. None the less, if you don’t think that information is valuable there is little need for a customers table.

That said, taking into considering the business model you have presented here I would define these tables.

  • orders
  • tickets (tickets that have been ordered in an order)
  • events (event details including ticket price, description, w/e else)

again… taking into consideration only what you have presented and nothing more cause I know your big on feature creeping…

So in the end something like this (again, this is only really meant to show the relationships between the tables)… I have no idea what other details there are. So with that…

orders:

  • id
  • (probably a bunch of customer details etc…)

tickets (tickets in an order)

  • id
  • orders_id (order that this “ticket” belongs to)
  • events_id (event ticket was order for)
  • quantity (number of tickets ordered for this event)
    ~ sale_price (not sold on this considering events will expire so I don’t think the price will change, right? Normally, though if you were selling physical items that have changed in price over time you would to store the price here… well not need but that would be simplest way to retain the information for historical purposes)
  • Unique key (orders_id,events_id) or make it a primary key and dump the surrogate key, whatever floats your boat.

events

  • id
  • ticket_price (price of a single ticket)
  • (who knows… whatever other information is necessary)

Disclaimer: This will not support reoccuring events in the best light. However, I did not see that as part of the business model. Additionally, it does not take into consideration multiple ticket prices per event… again not part of the scope outline. The only reason I say this is because your notorious for changing requirements… or not being as specific as you should be upfront expecting everyone else to just “know” undocumented requirements… we can’t. So yeah… for the sake of keeping this as *simple as possible requirements that have not been explicitly accounted for are not supported considering the more requirements the more complex the solution will need to be.

4

Sorry, day from hell at work yesterday?!

I would same they are one in the same.

Typically people “register” for things like Conferences, Speakers, Symposiums, etc.

And I suppose for more recreational things like Concerts, Carnivals, Shows, Dances, etc you just “Buy Tickets”.

why are you tracking attendees if you sell tickets? who cares if the punter attends once the ticket has been bought?

Well, I’m not “tracking” anyone at this point.

I have decided to make the person Buying Tickets/Registering to create an Account during Checkout - par for the course - but that is really just a Name, Email and Password so we can reach them if their are issues and so they can manage their Order/Registration.

I am leaning towards asking for Attendees names, just to help during the “check in” process. (A “Ticket” would have the Event Details, an Order Confirmation #, and the Attendees Name so the person checking people in could check people off the list or whatever. (But it isn’t like we store that or the Attendees have to Register. It is more just a convenience during Check-In and also would help make Name tags if needed.)

Does that answer your questions?

I would you I definitely need a CUSTOMER.

Not sure after that.

What does a CUSTOMER buy? An ORDER? A REGISTRATION?

And am I tracking ATTENDEES or TICKETS?

If Mr. Jones buys tickets for his family of 8 kids and his wife, we need some orderly way to tie those 10 people back to Mr. Jones and the fact that he did indeed pay for everyone.

It’s not really a big deal if his wife stays home and his youngest brings her friend - still 10 people and 10 tickets - but I think the poor person checking people in needs some way to be sure people showing up have proof that they or someone else paid for them to be there. (There is also the issue of managing people during breaks and what-not. Otherwise, lots of people could sneak in and we’d have no way of knowing.)

Make sense?

If I can get some help laying out a solid database design for this last area, then I think I have a decent database design and code build a nice “Event Registration Checkout” process.

Sincerely,

Debbie

5

I am trying to come up with a generic game-plan that I can possibly use for multiple client, although right now I am focused on this one Events Promoter that I may have a gig with.

Taking into account some other sites I am pursuing, I think it is a good idea to track Customers. (On related prior threads, I spent a lot of time learning how to: Create a Customer Account, Log-In/Log-Out, Create a Customer Profile, etc because I wanted to create online forums like SitePoint and get more Web 2.0ish)

So, let’s assume we want to know who a Customer is and store his/her info. (A “Customer” just being the person paying for things. Not the Attendees.)

* Professionally, I don’t think it’s very smart to not keep track of a single (unique) customers orders over time. None the less, if you don’t think that information is valuable there is little need for a customers table.

I agree.

That said, taking into considering the business model you have presented here I would define these tables.

  • orders
  • tickets (tickets that have been ordered in an order)
  • events (event details including ticket price, description, w/e else)

Well, this is what I drew out last week…

  • One CUSTOMER (payor) has one or more ORDERS
  • One ORDER has one or more TICKETS/ATTENDEES
  • One EVENT has one or more TICKETS/ATTENDEES

I think we came to similar conclusions, except here…

tickets (tickets in an order)

  • id
  • orders_id (order that this “ticket” belongs to)
  • events_id (event ticket was order for)
  • quantity (number of tickets ordered for this event)
    ~ sale_price (not sold on this considering events will expire so I don’t think the price will change, right? Normally, though if you were selling physical items that have changed in price over time you would to store the price here… well not need but that would be simplest way to retain the information for historical purposes)
  • Unique key (orders_id,events_id) or make it a primary key and dump the surrogate key, whatever floats your boat.

Visually we have…

CUSTOMER -||------|<-ORDER

ORDER -||--------|<-ATTENDEE/TICKET ->----------||-EVENT

Explained using an example…

CUSTOMER (Mike Brady)

ORDER (1234)

ATTENDEES
Mike
Greg
Carol
Peter
Bobby
Marcia
Jan
Cindy
Alice

TICKETS
0001
0002
0003
0004
0005
0006
0007
0008
0009

(If Alice was sick, Cindy could bring her friend “Lucy” in Alice’s place.)

EVENT
“Christmas Spectacular”
Mannheim Steamroller
Acme Theater
Los Angeles, CA
December 20, 2011
7:00-10:00pm

The ORDER would have a Quantity of 9 Attendees/Tickets.

Even though technically…

ORDER -<---------->-EVENTS

The ORDER could only ever be associated to ONE EVENT (Unlike normal Orders where you might order 20 different Products!!)

Each ATTENDEE/TICKET could have a different Price, Designation, Seating, and so on. (Maybe “Alice” got the “Senior Discount”?!)

Disclaimer: This will not support reoccuring events in the best light. However, I did not see that as part of the business model. Additionally, it does not take into consideration multiple ticket prices per event… again not part of the scope outline. The only reason I say this is because your notorious for changing requirements… or not being as specific as you should be upfront expecting everyone else to just “know” undocumented requirements… we can’t. So yeah… for the sake of keeping this as *simple as possible requirements that have not been explicitly accounted for are not supported considering the more requirements the more complex the solution will need to be.

Ouch!!

Why do you say that?! :frowning:

I am extremely detailed in most of my Threads and responses.

I don’t think I suffer from “Scope Creep”, but I do evolve what I want - or more like “further clarify” it!

Debbie

6

r937??

oddz??

Are you guys there??

Debbie

7

you’re doing fine, debbie, keep up the good work

8

You know, Rudy, there is no PER WORD CHARGE here at SitePoint… :wink:

Maybe you think I’m doing fine, but I still have several things to hash out.

From above…

Questions:
1.) What does a CUSTOMER buy?

An ORDER?

A REGISTRATION?

2.) And am I tracking ATTENDEES or TICKETS?

3.) ORDERS to EVENTS is a many-to-many relationship, and one EVENT better have many ORDERS, however, one ORDER can only ever have one EVENT, then how to I model and enforce that in MySQL?

Am I modeling things wrong?

Or am I modeling things correctly but just need some way to enforce things?

4.) What is a reasonable way to manage ATTENDEES at an EVENT?

As mentioned above, if I just have TICKETS as the junction between ORDERS and EVENTS, then you lose the human-aspect during checking in and what-not?!

I know some people find it invasive, but don’t you think I should be asking for a Full Name - or at least First Name/Last Initial - for each ATTENDEE?

What makes sense?

Please help me get a better grasp of these and other concepts so I can build a “smart” solution for my Events Promoter and other things I may do down the road.

And I’d still like to hear your thoughts too, oddz! :cool:

Thanks,

Debbie

9

so it would seem :cool:

i do not know what your customers should buy, but if you ask me whether they should buy an order or a registration, i would probably have them buy a ticket

i do not know whether you want to track attendees or tickets, but if i’m checking people at the gate, it’s a given that they’re attending, so i would look for their tickets

if one ORDER can only ever have one EVENT, then ORDERS to EVENTS is ~not~ a many-to-many relationship

a reasonable way to manage ATTENDEES at an EVENT is to ensure they don’t sit in the wrong seat

i don’t think you should be asking for a Full Name - or at least First Name/Last Initial - for each ATTENDEE, because if they don’t have a ticket, they can’t get in, right?

okay, i know these answers might come across as flippant, but the point i’m trying to get across to you is that the data model is detemined by business rules, which is a technical term for how the business operates in the real world, and i don’t really know how your business works

please don’t ask me how to run an event management business, because that’s not my domain of expertise

10

I should have asked, if someone is buying 6 tickets for their family to see a show, is that transaction more an “Order” or a “Registration”?

i do not know whether you want to track attendees or tickets, but if i’m checking people at the gate, it’s a given that they’re attending, so i would look for their tickets

Do most Events just deal with and track “Tickets”, or do they get personal and track “Attendees”?

if one ORDER can only ever have one EVENT, then ORDERS to EVENTS is ~not~ a many-to-many relationship

Then what is it?

(I always get confused about this…

A particular “Order” would only ever be for one “Event”, but the group of all “Orders” map to many “Events”.

A particular “Event” would hopefully have many “Orders”/“Tickets”/“Attendees”, and the group of all “Events” map to many “Orders”.

Follow my confusion?)

a reasonable way to manage ATTENDEES at an EVENT is to ensure they don’t sit in the wrong seat

Well, I don’t believe there is “assigned seating” at most of these Events. It is General Seating.

i don’t think you should be asking for a Full Name - or at least First Name/Last Initial - for each ATTENDEE, because if they don’t have a ticket, they can’t get in, right?

I don’t know?

I guess I was thinking, that if a person (e.g. Eddie Munster) didn’t have a ticket, then he could say, “I am Eddie Munster and my dad Herman Munster bought the tickets.”

Knowing that would be a backup.

Maybe just using a “Ticket #” or a “Confirmation #” would be enough?

What does everyone else think?

okay, i know these answers might come across as flippant, but the point i’m trying to get across to you is that the data model is determined by business rules, which is a technical term for how the business operates in the real world, and i don’t really know how your business works

A-ha! But I am not entirely sure what is the best way to run the business either?! (Part of being an Analyst is helping your client figure out what is the best solution to their needs. Because this potential client doesn’t have a website to register people, this is sorta new territory for both of us. He told me that if I could come up with a good prototype that he’d let me build an E-commerce Site/Event Registration Site for him. So I am trying to think of design decisions that not only make sense for Day 1, but a few years down the road.

There are not necessarily “right” or “wrong” decisions, but some choices are probably much more scalable (e.g. having the ability to charge different prices for Tickets to the Same Event.).

please don’t ask me how to run an event management business, because that’s not my domain of expertise

I wasn’t. I am asking for help on figuring out Entities and Relationships in the real-world and then translating them to Database Concepts. (You are quite good at that!) :wink:

Debbie

11

I would use a data model similar to this:

tblCustomer

custID - PK

tblOrders

orderID - PK
eventID - FK
custID - FK

tblEvents

eventID - PK

tblTickets

ticketID - PK
eventID - FK

For

I guess I was thinking, that if a person (e.g. Eddie Munster) didn’t have a ticket, then he could say, “I am Eddie Munster and my dad Herman Munster bought the tickets.”

you can add appropriate fields to tblTickets to store “meta” information relevant to each ticket.

Before you create your database and do any coding, make sure you have an ERD (Entity Relationship Diagram) for your database and that it is correct.

The last thing you want is to find half way through your coding that your database model is incorrect.

12

I’m confused here. Are you claiming to your “client” that you are an “Analyst”?

Is your client paying you for what you produce for them and does your client know you are basically stuck at step 1 and are resorting to trying to get someone in a chat room to at least help you if not actually do the “analysis” for you?

What you are asking help for is basically Systems Analysis 101 and Database Design 101 and any “real analyst” should not have any significant issues in coming up with a database model (ERD) that meets your client’s business model.

13

Except that one Order can only ever be for one Event.

Here is a Halloween based example to demonstrate how it would work in the real world…

:jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1:
[COLOR=“#A52A2A”]- Herman Munster is a CUSTOMER.

  • Herman Munster wants to attend the “2011 Halloween Ball” which is a SHOW (i.e. abstraction of the physical happening)

  • The SHOW is held at different VENUES (i.e. locations) at different Dates/Times.

  • An EVENT is the combination of a SHOW at a particular VENUE on a particular DATE/TIME

  • The EVENT is the physical thing people attend.

  • For a given SHOW, there are usually many EVENTS so people have choices of when and where to attend (just like most rock concerts)

  • Herman Munster wants to go to the “2011 Halloween Ball” - which is a SHOW - that he has heard so much about.

  • Because this is a new website, there is currently only one SHOW offered for purchase (i.e. “2011 Halloween Ball”)

(There is mention, though of upcoming SHOWS…)

  • Herman is ready to make a purchase and starts the shopping process.

  • First the website displays a list of Upcoming EVENTS (for the “2011 Halloween Ball” SHOW)…

    • Fri, Oct 28, 9:00pm-Midnight
    • Sat, Oct 29, 9:00pm-Midnight
    • Mon, Oct 31, 7:00pm-10:00pm

  • Herman chooses the “Sat, Oct 29, 9:00pm-Midnight” EVENT. (Only one EVENT can be chosen since the choices are mutually exclusive.)

  • Being a good father, Herman also wants to bring along…

    • Lily Munster
    • Grandpa
    • Eddie Munster
    • Marilyn Munster
    • The Raven
    • Spot (Dragon’s like to party too!)

  • The website asks for the “# of ATTENDEES” and Herman selects “7”.

**For now, the website just asks for “# of ATTENDEES” and Herman selects “7”. Later on, there will likely be a way to choose things for each ATTENDEE (e.g. “Senior” discounts, “Monsters get in 1/2-Price!!” discounts, “Kid” discounts, etc.)

  • Herman’s Order Total is $165.

  • Herman Munster (a CUSTOMER) decides to place an ORDER (unless you have a better name) to purchase 7 TICKETS for 7 ATTENDEES for the “2011 Halloween Ball” (SHOW) held at the Transylvania Hotel on Sat, Oct 29 from 9:00pm-Midnight (EVENT).

  • Again, Herman’s ORDER will only ever have one EVENT since that is how the checkout process works. (Just like you don’t buy airline tickets for separate vacations all in one purchase.)

  • The CUSTOMER/ATTENDEES have “General Admission” TICKETS.

  • The Event’s Promoter would like some way to help expedite “Check-In” and thinks maybe having NAMES on the TICKETS would help?!

  • If an ATTENDEE cannot attend, another ATTENDEE may go in that PERSON’S/THING’S place if the PRICE CLASSIFICATION is the same!! (So, Marilyn’s boyfriend cannot go in place of “Spot” since he is not a Monster which gets 1/2 price off!!!)

  • Someday, the Event’s Planner will have a Scanner to scan in bar Codes on each TICKET, but for now, he would like something that is flexible and that helps his CUSTOMER and ATTENDEES out if they lose their TICKET on the way to the EVENT, or something else happens.
    [/COLOR]:jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1: :jack1:

There is the “Business Model” as I see it, r937… :wink:

Sincerely,

Debbie

14

I’ve been working as a Business Systems Analyst for nearly the last 20 years. I’m not “claiming” anything. I am an Analyst!!!

I am not a Web Developer or Web Programmer or Architect or Database Designer.

All I do in my free time is dabble with web development because I am a techie at heart, and I want to strengthen my Web Development and E-commerce skills.

Is your client paying you for what you produce for them and does your client know you are basically stuck at step 1 and are resorting to trying to get someone in a chat room to at least help you if not actually do the “analysis” for you?

In the past few months I have seen some potential projects I could take on if I can get better with building websites. I proposed some ideas to an Events Promoter and he said that if I could show him some working prototypes of what I was suggesting, that he might be willing to give me some work.

I also am tinkering with an e-commerce site that I already built for myself, and am trying to learn more web development thingies to make it more interactive and Web 2.0-ish.

What you are asking help for is basically Systems Analysis 101 and Database Design 101 and any “real analyst” should not have any significant issues in coming up with a database model (ERD) that meets your client’s business model.

If I was a “Systems Analyst” you would be right. And if I was a “Database Architect” you would also be right. When did I say that I was either? (Do you know the difference between an “SA”, “BSA”, “BA”, “Analyst” and “Architect”? There is quite a difference…)

Please don’t waste an otherwise good thread with personal attacks…

Debbie

15

Except that one Order can only ever be for one Event

That to me implies that if a customer wants to order tickets say for the Halloween Ball and a New Years Eve Ball in the one session, they cannot. That’s not what I would advise my client, but if that is what your client insists on, then no problem.

Just reset the PK’s and FK’s in the data model I posted to restrict one event per order.

16

When did I say that I was either?

I never said you did say you were either.

That is why I asked

Are you claiming to your “client” that you are an “Analyst”?

Is your client paying you for what you produce for them and does your client know you are basically stuck at step 1 and are resorting to trying to get someone in a chat room to at least help you if not actually do the “analysis” for you?

What you are asking help for is basically Systems Analysis 101 and Database Design 101 and any “real analyst” should not have any significant issues in coming up with a database model (ERD) that meets your client’s business model.

17

Would you go to TicketMaster and buy tickets for “The Rolling Stones” and “Katy Perry” in one session… :cool:

Debbie

18

[QUOTE=webdev1958,post:16,topic:92875"]
I never said you did say you were either.
[/quote]

Well the tone of your earlier message sounded pretty personal.

I am a hobbyist Web Developer who saw an opportunity to challenge my newbie skills and maybe make some side income. That is what I presented…

Let’s not get insulting or personal here… :nono:

I never claimed to be a “Systems Analyst” or “Database Designer” and never implied either in this thread or to this guy I’m trying to pitch my decent Web Development skills too.

If you are going to contribute, then please stay on topic and stop attacking my intelligence…

Debbie

19

that’s fine.

I never attacked you. I simply asked questions because it appeared in your posts that you were charging your client and it appears from your comments and questions that you have very little idea about how to approach this project and that you are trying to run before you can even walk.

Since you are hoping to make some money from your projects, then I don’t post code for free for someone to simply use and then profit from.

I have given you the data model I would start with and build on and told you how to adjust it to meet your client’s business requirements. I am not going to give you the actual code as well - well not for free at least :wink:

What you are trying to do has been done countless times in all sorts of web based ticketing/registration/order systems and is not rocket science for anyone qualified.

If you haven’t already done so, perhaps consider doing an accredited website development course before tackling projects of this size and scope otherwise I suspect you’ll be floating around this and other chat rooms constantly looking for help.

Good luck :slight_smile: