locked
what is the best way to store calendar information/appointments. DB vs RRS feed

  • Question

  • Hi, 

    I'm looking at developing a Calendar web app.

    Anyhow, my only concern is whether it is worth storing the data in a database; whereas if I use XML or of the such.

    Additionally, if possible to point out the cons and pros of each approach, since I sort of have doubts.Perhaps XML's structure may be the perfect solution.(Thus saving on 25 thousand rows when they may just be set in XML as attributes)

     

    Thanks in advance,

    And I appreciate your time  

    Wednesday, February 23, 2011 8:32 PM

Answers

  • There are plenty of examples of Calendar tables, see one of them

    CREATE TABLE dbo.Calendar
    (
        dt SMALLDATETIME
            PRIMARY KEY CLUSTERED,
            
        isWeekday AS CONVERT(BIT, CASE
            WHEN DATEPART(DW, dt) IN (1,7)
            THEN 0 ELSE 1 END),
     
        isHoliday BIT NOT NULL DEFAULT 0,
     
        Y AS CONVERT(SMALLINT, YEAR(dt)),
        
        FY AS CONVERT(SMALLINT, CASE
            WHEN MONTH(dt) < 5 THEN YEAR(dt)-1
            ELSE YEAR(dt) END),
     
        Q AS CONVERT(TINYINT, CASE
            WHEN MONTH(dt) < 2 THEN 4
            WHEN MONTH(dt) < 5 THEN 1
            WHEN MONTH(dt) < 8 THEN 2
            WHEN MONTH(dt) < 11 THEN 3
            ELSE 4 END),
     
        M AS CONVERT(TINYINT, MONTH(dt)),
        D AS CONVERT(TINYINT, DAY(dt)),
        DW AS CONVERT(TINYINT, DATEPART(DW, dt)),
     
        monthname AS CONVERT(VARCHAR(9), DATENAME(MONTH, dt)),
        dayname AS CONVERT(VARCHAR(9), DATENAME(DW, dt)),
     
        W AS CONVERT(TINYINT, DATEPART(WK, dt))
    )
    GO
    INSERT Calendar(dt)
        SELECT DATEADD(day, n, '20000101')
            FROM numbers
            WHERE n <= 10957
            ORDER BY n


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 24, 2011 6:39 AM
  • Hi,

    Thanks for your question.

    From my point of view, it is appropriate to use XML file as data store for same amount of data and the performance is not so critical, which is also easy for management. If you have large number of data, you may consider using relational database management system (RDBMS) database such as SQL Server, which could make your more fiexible and also high availability, disaster recovery, etc. features. However, it is more complicated to use a RDBMS database.

    Based on your description, you could use XML file as database and test this configuration on your test environment before moving to production server. If it could well meet your business requirements, it should be fine. Otherwise, you could try to use a RDBMS database such as a SQL Server database.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, February 28, 2011 7:55 AM

All replies

  • There are plenty of examples of Calendar tables, see one of them

    CREATE TABLE dbo.Calendar
    (
        dt SMALLDATETIME
            PRIMARY KEY CLUSTERED,
            
        isWeekday AS CONVERT(BIT, CASE
            WHEN DATEPART(DW, dt) IN (1,7)
            THEN 0 ELSE 1 END),
     
        isHoliday BIT NOT NULL DEFAULT 0,
     
        Y AS CONVERT(SMALLINT, YEAR(dt)),
        
        FY AS CONVERT(SMALLINT, CASE
            WHEN MONTH(dt) < 5 THEN YEAR(dt)-1
            ELSE YEAR(dt) END),
     
        Q AS CONVERT(TINYINT, CASE
            WHEN MONTH(dt) < 2 THEN 4
            WHEN MONTH(dt) < 5 THEN 1
            WHEN MONTH(dt) < 8 THEN 2
            WHEN MONTH(dt) < 11 THEN 3
            ELSE 4 END),
     
        M AS CONVERT(TINYINT, MONTH(dt)),
        D AS CONVERT(TINYINT, DAY(dt)),
        DW AS CONVERT(TINYINT, DATEPART(DW, dt)),
     
        monthname AS CONVERT(VARCHAR(9), DATENAME(MONTH, dt)),
        dayname AS CONVERT(VARCHAR(9), DATENAME(DW, dt)),
     
        W AS CONVERT(TINYINT, DATEPART(WK, dt))
    )
    GO
    INSERT Calendar(dt)
        SELECT DATEADD(day, n, '20000101')
            FROM numbers
            WHERE n <= 10957
            ORDER BY n


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, February 24, 2011 6:39 AM
  • Hi,

    Thanks for your question.

    From my point of view, it is appropriate to use XML file as data store for same amount of data and the performance is not so critical, which is also easy for management. If you have large number of data, you may consider using relational database management system (RDBMS) database such as SQL Server, which could make your more fiexible and also high availability, disaster recovery, etc. features. However, it is more complicated to use a RDBMS database.

    Based on your description, you could use XML file as database and test this configuration on your test environment before moving to production server. If it could well meet your business requirements, it should be fine. Otherwise, you could try to use a RDBMS database such as a SQL Server database.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, February 28, 2011 7:55 AM
  • Any progress?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, March 1, 2011 5:55 AM
  • Thank you all for your replies!

    Firstly, I'd like to stress my results:

    When I posted my question, I had in mind a system with constant dates. For example, the company posts the dates and the times for services. Afterwards, a customer logs in and chooses the specific service record(the constant date and time), which was predefined by the admin. Once a dated is booked, then it is deleted from the system (the actual service information is saved in a Booking Table-DB.) It basically works like a seat-booking for a stadium; You have 25 available seats(which are numbered randomly,) and once the seat is utilized, it is deleted from the choice list.

    This system could somewhat work well with a DB and XML, I think(I would actually like to other's opinion); in actuality I would probably use a DB(for simplicity and speed)

    However, it appears that the booking system necessary is unlimited to the amount of appointments it could have for a given time. As a result, I say that a DB would be a help; since I am looking at more records and information.

    In a way I haven't really explained the reason for utilizing a DB rather than an XML. However, if anyone could explain in a better way, the reason for a DB over XML, or why use XML at all, it would very helpful/ educational.

    Thank you all for your help

    Hoping for a response!

    Thanks

    Tuesday, March 1, 2011 5:22 PM