Logic behind creating mysql tables

software development

#1

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


#2

Hi,

I’d do it slightly differently with 3:

having a location table

id
location

course table

id
coursename

with just the list of locations where classes can be taken & the course 2 tables

And a 3rd class table

id
location (fk of lk table)
month
day
course (fk to course table)

This would be my first go at it… Not sure it helps… But you never know

Tim

JagaLaw


#3

yes, it is starting to make sense.

Session Table (this will be a fixed table, never changes, just add it all at once.)

  • session_key
  • session_month (I think this is necessary as end of June displays under July)
  • session_year (necessary cause I will be displaying up to 6mos. of classes at a time.)

Trainers Table

  • trainer_key
  • trainer_name
  • trainer_phone/extension
  • trainer_email

Class Table

  • class_key
  • class_numberandname (these can go together)
  • class_description
  • description_link (link to the description under coursedescriptions.php

City Table

  • city_key
  • city_name (don’t need the location in the city as an afterthought)
  • city_link (link to that city page, more for the full calendar than the city pages)

New_Table
courses_id
session_key
city_key
class_key
month_day_start
month_day_end

am I close?


#4

Hi,

I think you should be able to loose these two link fields:

  • description_link
  • city_link

Given you will probably be querying the tables on the page where the link is anyway you could just send it to a generic page with an id number attatched, pull that out at the other end and display the content.

eg cityinfo.php?cityid=1

This would reduce that amount of work you needed to do, if you desperately wanted static pages you can always use a module in appache called mor_rewrite that will give the impression the pages are static.

Overall though unless your expecting hich traffic and lots of database queries I dont think its worth normalising it any further, if you are there are more changes you could do.

With regard to data type would recommend:

  • session_month & session_year only store 2 digit number

Other than that think it looks good

Tim

JagaLaw