NYCPHP Meetup

[Dan Cech]

Ok,

The first question is what kind of relationship are you trying to model 
here?

Is it:
- one-one   - each host is matched to a single guest
- one-many  - each host may be matched to many guests
- many-one  - each guest may be matched to multiple hosts
- many-many - each host may be matched to many guests and each guest may 
be matched to many hosts

If it is 1-1, then you would only need a column in the host table to 
hold the id of the matched guest, with a unique index to prevent 
multiple hosts being matched to the same guest.  You could also do this 
in reverse (add a column to the guest table to hold the id of the 
matched host)

If it is 1-many or many-1 you do the same thing but without the unique index

If it is many-many you will need a third table to hold the matches. 
This table would have at least 2 fields to hold the host id and guest 
id.  You may want to add other fields such as the event or user (if I am 
on the right track in terms of your application)

Dan

Aaron Fischer wrote:
> Greetings,
> 
> I'm about to build a little mini application and would appreciate a 
> little feedback/advice.
> 
> The application allows for a user to log in and view a list of hosts and 
> guests.  The data is stored in two MySQL tables (one for hosts and one 
> for guests).
> 
> The user will be able to pick a host and match them to a guest.
> 
> My plan is to have a column called match_id in both the host and guest 
> table.  When someone selects a host and guest, the app will insert a 
> unique id into both the host and guest record.  My plan was to use a 
> date/time stamp (including seconds) to create the unique match_id.
> 
> Another page view will display the list of matched hosts and guests, 
> where I will query both tables and join them by the match_id.
> 
> So, here are my questions:
> 
> Is there a better way to create a unique id than the time/date stamp?
> What would happen if two users are logged in at the same time and make a 
> match at the exact same time?  Not sure how MySQL handles when requests 
> are being made at the same time.  Would it barf?  Could it potentially 
> create two identical date/time stamps for two different matched groups?  
> (That would be very bad, given how I have designed the system so far).
> 
> Thanks in advance for any info and/or pointers, or even just validation 
> that I'm on the right track.  =)
> 
> -Aaron
> 
> 
> _______________________________________________
> New York PHP Talk Mailing List
> AMP Technology
> Supporting Apache, MySQL and PHP
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.nyphp.org