By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Event Registration with Multiple Tables

Brilstern
100+
P: 207
I am struggling to wrap my head around a data model, and I can't seem to find the right answer on the Google's...

I am working on setting up an event registration database (and eventually website) and I need to be able to register as a member, have members register members, and members register guest. I will include my current tables.

Expand|Select|Wrap|Line Numbers
  1. tblMember
  2. -----------------------------
  3. MemberID        Int       PK 
  4. MemberFName     VarChar
  5. MemberLName     VarChar
  6.  
  7. tblEvent
  8. -----------------------------
  9. EventID         Int       PK 
  10. EventName       VarChar
  11. EventVenue      VarChar
  12.  
  13. tblGuest
  14. -----------------------------
  15. GuestID         Int       PK 
  16. MemberID        VarChar   FK 
  17. GuestFName      VarChar
  18. GuestLName      VarChar
  19.  
  20. tblRegistration
  21. -----------------------------
  22. RegistrationID  Int       PK 
  23. MemberID        Int       FK 
  24. EventID         Int       FK 
I understand how to create registrations for the event for a member, but I am struggling to include the guest as well. Any help would be much appreciated.
Aug 23 '16 #1

✓ answered by twinnyfo

Hey Stevan,

There may be several ways to skin this cat. but I think the best option, on the surface, is below.

Instead of having a table for Members and another for Guests, have one Table for Attendees, with a Yes/No Flag for indicating "Member". Those with "True" are members, and those with "False" are Guests. There would also be a field for "Referral" (for lack of a better term), which would be the Member who registered the Guest (it would refer to itself). Then the Registration would have AttendeeID. This would allow you to quickly look at the registration for any event and identify how many members were attending, how many guests AND which members signed in the most guests (I don't know if that is something that you are planning to track).

I hope this all makes sense. More importantly, I hope this hepps!

Let me know if you have any additional questions.

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,184
Hey Stevan,

There may be several ways to skin this cat. but I think the best option, on the surface, is below.

Instead of having a table for Members and another for Guests, have one Table for Attendees, with a Yes/No Flag for indicating "Member". Those with "True" are members, and those with "False" are Guests. There would also be a field for "Referral" (for lack of a better term), which would be the Member who registered the Guest (it would refer to itself). Then the Registration would have AttendeeID. This would allow you to quickly look at the registration for any event and identify how many members were attending, how many guests AND which members signed in the most guests (I don't know if that is something that you are planning to track).

I hope this all makes sense. More importantly, I hope this hepps!

Let me know if you have any additional questions.
Aug 24 '16 #2

Brilstern
100+
P: 207
It hepps! JK lol had to get your typo in there :)

Hmm.. I see your point and why you would suggest that. To be honest, putting the data in one table never occurred to me, and for no reason other than my own head it sounds scary...

That being said.
A little background.

I am working on this for a non-profit who has sustaining members, which involves a lot more detail (another 10 tables or so) on the member side. The event feature is really separate of the membership side other than I am attempting to keep the functions in the same database simply for data reporting ease and leveraging already known data, instead of my users having to sign up with new data every time.

But this has inspired a thought. What if I added a table:
Expand|Select|Wrap|Line Numbers
  1. tblAttendee
  2. -----------------------------
  3. AttendeeID      Int       PK
  4. MemberID        Int
  5. GuestID         Int
  6. AttendeeFName   VarChar
  7. AttendeeLName   VarChar
I use my guest table and member table to feed a lookup form which would allow me to leverage the data already there, but it also allows me to enter a new guest or a member, both creating other entry's into the proper tables and also filling my attendees list as well.

The only issue there is my MemberID field now contains different types of "MemberIDs".

Now I don't like it anymore...
Aug 24 '16 #3

jforbes
Expert 100+
P: 1,107
I would do it the way Twinnyfo suggests, it's the epitome of normalization.

A couple of minor options you could have is to use a single field for AttendeeType in the case were an attendee could only be either a Member or Guest, but that is really minor.

Another, but probably controversial way of segregating the Attendee's Table is to build in the distinction between Member and Guest into the PrimaryKey. So you would have a PK for Members that start with "M" giving you the pattern M00001, M00002, M00003...M99999 and then use "G" as a prefix for the Guests, giving the pattern of G00001-G99999. The greatest benefit of embedding the type in the PK is that the PK can then be printed on documentation and humans can easily identify the difference between a Member and a Guest just by their ID Number. ... If it was on a nametag, it would also give them a gauge on how long someone has been a Member. I'm not saying that this should be done, just an option to consider.
Aug 25 '16 #4

twinnyfo
Expert Mod 2.5K+
P: 3,184
BTW, "heppin'" is not a typo... It's just what we try to do here on Bytes. Please let us know of your success in this project and any additional means of assistance we can provide.
Aug 25 '16 #5

NeoPa
Expert Mod 15k+
P: 31,347
Hi Stevan.

I'm with the others on this one. A single table to hold people makes better sense than having two tables do similar jobs.

Having a joining table to filter them into a single entity is just a half-way and has little going for it, while introducing unnecessary complications ;-)

For TwinnyFo and his hepp, just think of what you say being pronounced by someone from the Deep South ;-) You may even get to work out what number his nick refers to.
Aug 25 '16 #6

Brilstern
100+
P: 207
Hmm. Well from a non-database expert, normalization is not where my mind goes to. :) But I understand the importance for sure.

I actually considered the distinction of the PK with a designator. Still on the fence on whether I like it, or if I will look more at a true/false OR member/guest field... We will see.

As far as the hepp... I get it now. Being from Texas we tend to understand things like "I'm fittin to do it" :) And the nick number comes in quite clear now haha.

Always a pleasure gents, let's hope the team like normalization.
Aug 25 '16 #7

Post your reply

Sign in to post your reply or Sign up for a free account.