Member Avatar for vish_yagnik

I Have one table in sql

Date Day Start Time End Time Subject Teacher
2014-06-02 Monday 2:30 PM 04:30 PM Maths Mr.Smith
2014-06-10 Tuesday 2:30 PM 04:30 PM Science Mr.Ayar
2014-06-16 Monday 2:30 PM 04:30 PM Maths Mr.Smith
2014-06-02 Monday 3:30 PM 04:30 PM Maths Mr.Smith

I want to make query to check Mr.Smith is already engaged in inserted time slot

Edited by vish_yagnik
  • 2 Contributors
  • 1 Reply
  • 250 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for diafol

This table is the wrong way to go. It should be relational:

period_id (PK) | tsstart    | tsend      | subject_id (FK) | teacher_id (FK)
1              | 1401888151 | 1401898151 | 4               | 1

PK = primary key, FK = foreign key

teacher_id (PK) | title_id (FK) | firstname | lastname | code | gender
----------------------------------------------------------------------
1               |   3           |  Amelia   | Davies   | agd  | f

subject_id (PK) | subject | shortcode
-------------------------------------
4               | Science | sc

title_id (PK) | title
---------------------
1             | Mr.

That's just a small sample of the way I usually approach timetabling. Notice the ts fields - unix timestamp - this could be datetime instead

One thing though - it's a lot of work to keep adding periods to this table, for every single day,e very week of the academic year. Ridiculous amount of work - so you don't need to do it like that. Create a timetable - most UK schools have a fortnightly timetable - and use that as a template - no need to hard code dates. You only need to track if it is week1 or week2 for fortnightly timetables. This is one method I've used...

id | week_no | day_no | lesson_no | subject_id | room_id | staff_id
-------------------------------------------------------------------
 1      1        3        4           7           29         9

So that's your timetable item: week1, day3 (Wednesday), lesson/period 4

You then just need to keep a week_date table... (dates start on a Monday)

id | date       | week_no
-------------------------
1  | 2014-06-02 |  1
2  | 2014-06-09 |  2
3  | 2014-06-16 |  1
4  | 2014-06-23 |  2
5  | 2014-06-30 |  1

Anyway, just a few ideas.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.