Event Registration with Multiple Tables

[Brilstern]

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.

tblMember
-----------------------------
MemberID        Int       PK 
MemberFName     VarChar
MemberLName     VarChar
 
tblEvent
-----------------------------
EventID         Int       PK 
EventName       VarChar
EventVenue      VarChar
 
tblGuest
-----------------------------
GuestID         Int       PK 
MemberID        VarChar   FK 
GuestFName      VarChar
GuestLName      VarChar
 
tblRegistration
-----------------------------
RegistrationID  Int       PK 
MemberID        Int       FK 
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.

[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.

[Brilstern]

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:

tblAttendee
-----------------------------
AttendeeID      Int       PK
MemberID        Int
GuestID         Int
AttendeeFName   VarChar
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...

[jforbes]

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.

[twinnyfo]

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.

[NeoPa]

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.

[Brilstern]

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.