DB Table Structure Assistance

[woldie]

Hi,

I'm currently developing a booking in system, which is going really well. However I'm a bit stuck on how to go about defining a table structure.

What we have is 3 engineers doing assessments on each day of the meeting, but we have 6 engineers in total so therefore we will be rotating for each of the days, but what I'm trying to do is to create a table so that each day has 3 engineers.

Table structure I already have:

Engineers

engind int PR
engname varchar(255)

Shows (current is used to define which is the current show)

shid int(11)
showname varchar(255) ¦
current int(11)

Showdates_map

shmid int(11) PR
shid int(11)
showdate date

So the aim here is to have 3 engineers for each day of the meeting depending on the values given in the showdates_map table.

Many Thanks

:o)

[ergophobe]

Am I right in assuming that shows are recurring, and that's why you have a table for shows and one for showdates? If a show is a unique event that occurs only once, then you should have this in one table.

So assuming that there is a small set of shows and each show in that set will be given on various dates, and each performance will require three engineers...

table engineers_to_showdates

engid int(11)
shmid int(11)

Then

SELECT showname, showdate, engname
FROM Engineers, Shows, Showdates, engineers_to_showdates
WHERE engineers_to_showdates.engid = Engineers.engid
AND engineers_to_showdates.shmid = Showdates_map.shmid
AND Showdates_map.shid = Shows.shid
ORDER BY Showdates_map.showdate;

... roughly

[woldie]

Thanks ergophobe,

Yeah you've hit the nail right on the head!

'Am I right in assuming that shows are recurring, and that's why you have a table for shows and one for showdates?'

Yes, you are correct, we do attend several shows throughout the year, and thats why I have a shows table and map table to assign the dates for each show.

'So assuming that there is a small set of shows and each show in that set will be given on various dates, and each performance will require three engineers... '

Yes, thats right, on each given day of the show we have 3 engineers, so the possibility being that we could have the same engineer working on the 1st day of the show, and 3rd day of the show.

Thanks for the SQL query, I did test this out, and it works, so I will incorporate that into my system.

:o)