mySQL table relations or just use one table?! Need Help php mysql

Question

I'm trying to create a drop down select option using PHP, MySQL, and ajadx I have the PHP and ajax pretty much figured out but I'm stumped on how I should organize my tables I couldn't decide so I created it both ways:

The first way the tables are:

• CourseTbl containing (CourseID*, and CourseName)
• DateTbl, (DateID*, and DatesOffered(entered as a varchar unsure of how to do date to show two day span)
• LocationTbl (LocationID*, LocationName)

I figured I could try and connect the ID's for each but realized there is no way to connect them since their are some that repeat dates and some that are offered the same daye.

I also have the a table which just lists all of these together.

TableSelect (ID*, CourseName, DatesOffered, LocationName*)

If I wanted to make it as a drop down select would I have to create a subID that would be and INNER Join? Use an XML with PHP? I just keep asking myself how can I input the information and query it properly?

How is the best way to attack this? any help would be great and thanks for your time!

Answer 1

It probably would be beneficial for you to store all this data in a single table. The reason I say this is your queries would be a lot less intense (you are not joining on multiple tables) only doing a select from one table then and if you structure your data right you can use the output exactly as is to populate your dropdown boxes.

An interesting methodology to follow is the one where you try to think of what you want to accomplish first and how the end result is going to look and work. Then you design your DB to accommodate you with this structure.

I have found that if your DB structure is right and you serve your php script the data as it needs it, your site is amazingly scalable. I mean it makes sense to use a single select rather than a inner join on 3 tables right?

You would also sit with one set of ID's and no foreign keys etc to keep data integrity. If I read your explanation right then I see the data relates exactly to each other and its always good to group the same data together.