Open/Closed dates with ASP and SQL

[oldmuttonhead]

I'm not new with ASP or SQL, but I can't for the life of me think of a good way to manage this.

I have a project I'm working on for my group of sports officials. I need to be able to store the closed dates for each individual in the system. The individuals need to be able to add/remove their closed dates and athletic directors need to be able to search for offcials based on open dates.

What's going to be the best way to store the dates for each official? The normal relationships between the tables that I would setup for something like this seems silly in this instance. If someone could just point me in the right direction, I'd appreciate it!

I'm using SQL 2000 on a Windows 2000 Server. Thanks!

[miranda]

Create a table listing each day, or week if for sports where games are only played one day a week. This table could be a simple two column table with an integer field set as an identity no duplicates and also set as the primary key in one column and then a date timefield for the days in the other.

Then create another table for the officials listing each of the officials, and create a primary key on each of these.

Next create a third table we will call this table bookedOfficials. This table only needs two columns. One column for the primary key from the dates table lets call this dateID. and one column for the primary key from the officials column, lets call this officalsID. You need not set any keys in this table. This is a many to many relationship table.

Now, when any official is booked for a given day, a record is made in the bookedOfficials table. If that game is cancelled or they become available for some reason you would merely delete that record.

When you want to show who is booked for a given date you can run a query on the bookedOfficials table to find out who is booked and then run a 2nd query to display available officials like so:

Dim oRs, sSQL, sOfficials, boolSomeBooked
Set oRs = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT officialsID FROM bookedOfficials WHERE dateID = '" & dateVariable & "';"
oRs.Open sSQL, "your dsn or dsnless connection string here", 0,1
Do Until oRs.Eof
sOfficials = sOfficials & oRs("officialsID") & ","
oRs.MoveNext
Loop
oRs.Close

'Now display the available officials

If Len(sOfficials) > 0 Then boolSomeBooked = True
Response.Write "The following officals are available on " & dateVariable & ": "

If boolSomeBooked = False Then
sSQL = "SELECT officialsName FROM officialsTable;"
oRs.Open sSQL, "your dsn or dsnless connection string here", 0,1
Do Until oRs.Eof
Response.Write oRs("officialsName") & "<br>"
oRs.MoveNext
Loop
oRs.Close
Else
sOfficials = Left(sOfficials, Len(sOfficials)-1) 'remove the trailing comma
sSQL = "SELECT officialsName FROM officialsTable WHERE officialsID NOT IN (" & sOfficials & ");"
oRs.Open sSQL, "your dsn or dsnless connection string here", 0,1
If oRs.Eof Then Response.Write "No officials available for that date"
Do Until oRs.Eof
Response.Write oRs("officialsName") & "<br>"
oRs.MoveNext
Loop
oRs.Close
End If
Set oRs = nothing

[oldmuttonhead]

Create a table listing each day, or week if for sports where games are only played one day a week. This table could be a simple two column table with an integer field set as an identity no duplicates and also set as the primary key in one column and then a date timefield for the days in the other.

::SNIP::

Thanks for the response and sorry I'm replying so late.. I went out of town for the Holidays.

The part I quoted above is the part I was questioning. So if I need records for 10 years out, I would need to have 3650 records in that table each with it's own date? (I realize there are leap years in there) There's no way around creating a record for every day? That seems like a logistical nightmare. :eek:

[miranda]

A many to many table like I mention is actually quite common and is used in instances with a lot more records than you are talking about. This seems like a large table but it really is not a lot( there are databases with millions of records in them), and when there are only 2 columns the overhead is not very big.

But you need to understand, You only have the record when the official is booked on any given day. So you do not need 3650 records per official for 10 years worth of scheduling, unless the officials are booked for every day for 10 years! For days where the official is not booked no record will exist. Basically this table is just foreign keys to two other tables and links them together. The other two tables being the actual sports schedule(date, hometeam, awayteam, location, time, etc,) and the table that holds the official's information(name of offical, phone number, etc).

[oldmuttonhead]

A many to many table like I mention is actually quite common and is used in instances with a lot more records than you are talking about. This seems like a large table but it really is not a lot( there are databases with millions of records in them), and when there are only 2 columns the overhead is not very big.

But you need to understand, You only have the record when the official is booked on any given day. So you do not need 3650 records per official for 10 years worth of scheduling, unless the officials are booked for every day for 10 years! For days where the official is not booked no record will exist. Basically this table is just foreign keys to two other tables and links them together. The other two tables being the actual sports schedule(date, hometeam, awayteam, location, time, etc,) and the table that holds the official's information(name of offical, phone number, etc).

I'm not as concerned with the size of the database as I am about keeping it updated for years to come. I have no idea how far out they will want it. I personally only schedule out 3 years, but who knows how some of them are. :) Anyway, thanks again for your help! I really appreciate it!