Not sure if this will help or not, but do you needs the PollFK field in the times table. Surely your relationshp is Event --> Date --> Time. If you do a query with some outer joins you should then be able to return
Event 1 Date 1 Time 1 Event 1 Date 1 Time 2 Event 1 Date 2 Time 3 Event 2 Date 3 Time 4 Event 2 Date 3 Time 5 and so on. >I've been working on an event scheduling application for the past 2 weeks. >I'm pretty close to wrapping it up but have now run into an issue on my last >part of the display. I'm thinking my data model might be the issue and would >like to get some ideas from people on how I might be able to fix it. > >The way this thing works is: >Step 1. user Creates a Title and description for their event >Step 2. the user can select as many dates as they want >step 3. the user enters in multiple times for each date they selected > >The structure I'm using for storing the data looks like: > >Table 1 >PollID|UserID|Title|Description > >Table 2 >DateID|PollFK|DateValue > >Table 3 >DateFK|PollFK|TimeValue|Order > >This structure made the most sense to me only because they can add any >number of dates that they want so if they added 15 dates I create a new row >for each date and the ID for that date will then relate to the times they >enter in step 3. This is what one event might look like in these 3 tables > >Table 1 >PollID | UserID | Title | Description >1 25 My Title My Description > >Table 2 >DateID | PollFK | DateValue >1 1 2009-06-25 >2 1 2009-06-26 >3 1 2009-07-27 > >Table 3 >DateFK | PollFK | TimeValue | Order >1 1 9:00 AM 1 >1 1 10:00 AM 2 >1 1 12:00 PM 3 >2 1 11:00 AM 1 >2 1 12:00 PM 2 >3 1 6:30 PM 1 > >In My last display page I need to show a table similar to this: > > >June >July > Wed 25 Thur 26 > Wed 01 >9:00 AM 10:00 AM 12:00 PM 11:00 AM 12:00 PM 6:30 PM > >I'm just really struggling to get this layout done with multiple queries. >I'm doing one query to group the dates by month then going back and getting >the days that were entered in for that month which I can do fine but I can't >get the display to show up as I showed above. > >So I guess my 2 questions are: > >1. Does anyone see a more efficient way to store the dates and times, >keeping in mind that a user can add as many dates as they want, so creating >x amount of columns doesn't really seem feasible. > >2. What would be the best way to build my display page? > >Thanks, >Ben ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323195 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
