none
How to store many datetime fields?

    質問

  • I face the need to extend a database. I should store about 30 datetime fields somehow in a table. 30 columns is not a good idea I think. So what can you advice to store so much datetime fields? Maybe varchar with a specified delimeter?
    2012年2月20日 7:15

回答

  • A user can buy a ticket on 30 days (concrete days) chosen by himself.

      The I guess we can think for a table where we could have the details like follows

     

    CREATE TABLE TICKETDETAILS(USERID INT,TICKETDATE DATETIME)


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012年2月20日 12:19
  • As manish mentioned, you should consider storing all the dates in a different table and Join the table as necessary rather than denormalizing the dates. This will reduce your work of breaking the dates everytime you query for a selected date.

    If you need it in a single column for any specific reason, consider using XML.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    2012年2月20日 16:30

すべての返信

  • XML data type might be a choice. However, you may tell us why you need 30 datetime fields in the first place.

    Alex Feng | SQL Server DBA, ALIBABA.COM
    My Blog | MCTS: SQL Server 2008, Implementation and Maintenance
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2012年2月20日 8:40
  • I'm interested to know why you "must" store 30 datetime columns in a single relation. On the surface it sounds like you may want to revist the logical model of your database design.

    Perhaps you could share a little more infomration concerning your scenario?

    It's difficult to propose alternatives without first understanding what it is you are trying to model however an often typical alternative to what you are proposing would be to store a single datetime column and differentiate the values based on some sort type column, which itself would be a foreign key to a Type relation.


    John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter



    2012年2月20日 8:43
  • A user can buy a ticket on 30 days (concrete days) chosen by himself.
    2012年2月20日 11:58
  • A user can buy a ticket on 30 days (concrete days) chosen by himself.

      The I guess we can think for a table where we could have the details like follows

     

    CREATE TABLE TICKETDETAILS(USERID INT,TICKETDATE DATETIME)


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012年2月20日 12:19
  • A user can choose 30 different dates and you advice me to

    CREATE TABLE TICKETDETAILS(USERID INT,TICKETDATE DATETIME)

    And where do you want to store 30 datetime fields?

    2012年2月20日 15:44
  • As manish mentioned, you should consider storing all the dates in a different table and Join the table as necessary rather than denormalizing the dates. This will reduce your work of breaking the dates everytime you query for a selected date.

    If you need it in a single column for any specific reason, consider using XML.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    2012年2月20日 16:30