Hi, I am sitting here racking my brain on the best way to tackle mysql table creation for a project.
It's sort of a calendering system for classes in various cities, there will be one main calendar of cities (with classes (each class has a description elsewhere on the site and an exact location in that city) and dates for that particular city in that particular month.) And then each city has their own page displaying just their courses.
Here is the end result of how the main calendar is displayed.
June
Charleston at Fisher
101: June 11, 12
102: June 13
103: June 14
108: June 15
201: June 19
San Jose at Johns
102: June 13
108: June 15, 16, 17
203: June 18, 19, 20
July
Yuma at Rattlesnake Ridge
101: July 9
104: July 14
Tulsa at Backyard
106: July 12
105: July 14, 15, 16
So, I am thinking three tables...
-location-
city_key - primary key
city_name
city_location
-classes-
class_key - primary key
class_number
class_name
class_description
-dates-
date_key - primary key
date_month
date_day
date_year
date_city - foreign key of cities table - city_name
date_class - foreign key of class table - class_number
my problem as I see it is that in July I may have 2 different class sessions:
July
Yuma at Rattlesnake
101: July 12, 13
102: July 15, 16
Yuma at Rattlesnake
101: July 22, 23
102: July 24, 25
So, would I need to make a fourth table
-Sessions-
session_key primary key
session_city
then add session_key or to:
-location-
city_key - primary key
city_name
city_location
city_session
I haven't written tables in two years...any help is much appreciated.
thanks
barbara
