We help IT Professionals succeed at work.

Storing 365 day calendar entries in MS SQL 2000/ 2005

philwill4u
philwill4u asked
on
347 Views
Last Modified: 2012-08-13
I have a booking system where suppliers enter booking availability across 365 days in the year.  Some days are available and others are not and throughout the year the available dates will change.
I'm not keen on the idea of having 365 columns in a table and marking each one as available or not - seems very inefficient.

What is the best way to store calendars like this?  Do you have any code I can preview?  Coding in ASP and MS SQL 2005.

Many thanks
Comment
Watch Question

Commented:
Hi philwill4u,

How about a table something like this:

table AVAILABLE_DATES

date_id - PK - identity
date - datetime
available - integer

That way you can enter 365 rows and use the available column with a 1 for available and 0 otherwise (or whatever values you choose).

Alternatively, you could drop the available column, and ONLY have avaialble dates in the table, hence if a date is not in the table, then it is not available.

Also, if you have multiple suppliers, then you would add a supplier_id column to identifier which supplier has the date as available.

Cheers,
Hillwaaa
Another option could be something like this.  (Assuming you already have a supplier table with a supplier ID).  You could create a supplier_availability table with:

supplier_availability_id identity
supplier_id int
from_date small_datetime
to_date small_datetime
availability_code int (1 = available, 2 = not available, 3 = unknown, 4...5...6...etc.)

This way, if a supplier (id # 100) is available from Jan 1 - Feb 1, not available on Feb2-5 bu available again on Feb 6 - 28, the rows would look like this:

suplier_id      from_date      to_date      availability_code
100               01/01/2007    02/01/2007  1
100               02/02/2007    02/05/2007  2
100               02/06/2007   02/28/2007   1
100               03/01/2007   12/31/2007   3

CERTIFIED EXPERT

Commented:
Code? Calender Apps are big business example: http://www.sisuware.com/  sells souce code for ASPCal ~$166
Then there is aspWebCalendar, Edate, Mondo Calender etc etc

My initial thought is you'd have in a 'relational' database a years table a months table and a days table then a bookings table with month, day, year columns  . A view on the bookings would indicate if the calendar day shoud be booked or available color?

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
I take that back there is some free code:
http://www.asp-dev.com/main.asp?page=42
why not keep a table with 2 columns...

something like

create table tbl_dates (
    day_of_year datetime,
    locked bit
)

declare @i int
set @i = 1

while @i < 367
begin
      insert into tbl_dates values(@i, 0)
      set @i = @i + 1
end


select * from tbl_dates where day_of_year = datepart(dd,'12 jan 2007')
select * from tbl_dates where dateadd(dd, day_of_year-1, '1 jan 2007') = '12 jan 2007'

also you can update...

update tbl_dates
set locked = 0
where dateadd(dd, day_of_year-1, '1 jan 2007') = '12 jan 2007'

or

update tbl_dates
set locked = 1
where
day_of_year = datepart(dd,'12 jan 2007')

so you start with '1 jan 2007' or '1 jan 2008' or whatever year you want.

Author

Commented:
OK these are all great thoughts.

A little more information may be needed....

A supplier has many products.  Each product is unique and available at many locations.  For each location, there is a list of available dates.
Available dates may change from week to week.  The number of dates average 15 per month per product/ location combination.  There are over 1000 products, averaging 3 locations each = 3000.  

Estimate of the # dates held in database is 3000 x (15 days x 12 months) =  540 000

Product_ID  
Location_ID
Booking_Date

Implied that if its listed then it must be available.
To update, I could simply delete the records for the Product_ID and Location_ID combinations and reinsert the calendar entries each time.

Would this work ok?

Would this work ok?
Yep that would work fine.

Stop now and think though.... could you possibly have a number of statuses for each date/product/location or is it definitely discrete (on/off)?

If you code the whole thing up so that its discrete, (i.e. Yes or No) it'll probably be a real pain to change later to have multiple statuses.

On the other hand if you're sure that its discrete then you can probably derive some performance benefits from ~not~ having a status column.



Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I think what I'll do is copy the record to an audit file (it'll give me some good insights on the supplier's availability over time), then I'll delete the record off the main table.  

Also, I like the from and to date idea especially from an efficiency viewpoint.  There's a little more complexity involved in the searches and constuction of the from and to dates but not too much - its manageable.

Well... you've all been great help to me.  I think I now have what I need to progress.  Thanks very much for your help!

Commented:
No worries - and good luck!
Glad we could help, and good luck!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.