# Thread: A query for reserving seats

1. Registered User
Join Date
Apr 2010
Posts
3

## Unanswered: A query for reserving seats

Assume that i want to register online for an exam and after registration is complete, applicants will receive an entrance ID card.
To do so i have considered the following tables:
Days(day_id,day_name)
Schools(school_id, school _name)
Times(time_id,time_name)
Exame(exam_id,day_id, school _id,time_id,seat,student_id) --exame_id is identity
seats(day_id, school _id,time_id,seat,taken)
The exam will be held in a couple of days, and capacity of each school for each time is 10 students. Seats will be filled by DAY,SCHOOL and TMIE respectively. For example:
Day1, school1, time1; 10 students.
Day1, school1, time2; 10 students.

Day1, school1, last time; 10 students.
Day1, school2, time1; 10 students.
Day1, school2, time2; 10 students.
Day1, school2, last time; 10 students.
And so on.
The SEATS table indicates weather seats are taken or not. To enter input i have used the following algorithm:

insert into seats(day_id, school_id, time_id, seat)

SELECT day_id, school_id, time_id,

ROW_NUMBER() OVER(ORDER BY day_id, school_id, time_id, i) AS id

FROM days

CROSS JOIN schools

CROSS JOIN times

CROSS JOIN(SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10) AS d(i)

Now to get the next empty seat i need to run this query:

select top(1) * from seats where taken=0 order by

day_id,school_id,time_id,seat

so far there is no problem, now i want to add the capacity field to the school table and i do not know what query should be used to fill the seats table.

example:

Assume that the data in tables are this:
Days
day_id day_name
1 sunday 2010/03/23
2 Tuesday 2010/03/25
------------------------------------
Schools
school_id school_name
1 school1
2 school2
----------------------------------
Times
time_id time_name
1 9 - 10
2 10 - 11
-------------------------------------
If all seats reserved ,the exam table must be like this :
Exam

Day_id School_id Time_id Personal_id
1 1 1 1
1 1 1 2
1 1 2 3
1 1 2 4
1 2 1 5
1 2 1 6
1 2 2 7
1 2 2 8
2 1 1 9
2 1 1 10
2 1 2 11
2 1 2 12
2 2 1 13
2 2 1 14
2 2 2 15
2 2 2 16

---------------------------------
Consider data in tables days,schools,times , now seats table must be like this.
Day_id School_id Time_id seat taken
1 1 1 1 0
1 1 1 2 0
1 1 2 3 0
1 1 2 4 0
1 2 1 5 0
1 2 1 6 0
1 2 2 7 0
1 2 2 8 0
2 1 1 9 0
2 1 1 10 0
2 1 2 11 0
2 1 2 12 0
2 2 1 13 0
2 2 1 14 0
2 2 2 15 0
2 2 2 16 0
-----------------------------------------
When a clients wants to register ,we must figure out where we can register him?!!so we must find out the first free seat by this query:

select top(1) * from Places where taken=0 order by dayid,schoolid,timeid,seat

and then we use this information for insert into exam table and we update the seats table for new taken seat.(set taken=true)
now,the problem is I don’t now how to fill seats table(a query for initialize table [if we add the capacity field to the schools table])

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by mahdi87_gh
Days(day_id,day_name)
Times(time_id,time_name)
this always kills me

a table for day names, and somehow there is a perceived need to obfuscate them by assigning a surrogate key

let me guess, the id for sunday is 1, the id for monday is 2...

also, the time... what's wrong with actually using the time instead of giving each time a surrogate key?

3. Registered User
Join Date
Apr 2010
Posts
3
no, please look at the sample i used above.
the table days content is not like 1,sunday 2,monday,....

assume that we want to perform exam in two days,one 2010/03/23 and other 2010/03/29

4. Registered User
Join Date
Feb 2007
Posts
15
Hello mahdi87_gh,

Personally this is a very interesting challenge, I will take a look and try to help you.

By the way r937, for the very next time just read all the message until the end before criticize someone's question.

All the best.

OS

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
yeah, thanks for reminding me that sometimes i do slip up slightly

i should have said that using a surrogate key for a date (not a day name) is silly

just use the date!!!

the surrogate key is still useless

6. Registered User
Join Date
Apr 2010
Posts
3
Personally this is a very interesting challenge, I will take a look and try to help you.