Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Multiple Columns or Rows?

    Hello all, my skills and knowledge with database design is very limited. I need to create a database in MS SQL that will hold ALOT of data, and I could really use some tips.
    Data will be submitted to this db approx. every minute from nearly 20 different locations. I have about 20 individual integers that will be submitted each time.

    Should I design the db with the following columns:
    RowID, LocationID, Int1, Int2, Int3, Int4, Int5, Int6....

    or should I use a separate row for each Int record:
    RowID, RecordID, LocationID, Int

    The second option allows me to be more flexible with the number of Ints stored. For example, I will usually have about 20, there may be more or less at times. Which method will provide the fastest results when querying the data? The first method, I'll have about 30,000 rows per day each with about 20 columns.
    The second method,I'll have about 600,000 rows a day each with 4 columns.
    The db will hold about 1 week's worth of data at all times.
    Any tips or suggestions? Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you aware of Normalisation? Are you aware of First Normal Form?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The database as projected will not be all that large. What are the integer values? Are they related in anyway (beyond belonging to the same location)? If they represent the same thing (such as some measurement of one thing), then it will be MUCH easier to report on the data if it is all in one column. Averaging 20 columns over 30,000 rows will hurt your brain.

  4. #4
    Join Date
    May 2009
    Posts
    4
    Quote Originally Posted by pootle flump
    Are you aware of Normalisation? Are you aware of First Normal Form?
    I've heard the term but not familiar with it. Like I said, my knowledge is very limited. Should I start by researching this topic?

  5. #5
    Join Date
    May 2009
    Posts
    4
    Quote Originally Posted by MCrowley
    The database as projected will not be all that large. What are the integer values? Are they related in anyway (beyond belonging to the same location)? If they represent the same thing (such as some measurement of one thing), then it will be MUCH easier to report on the data if it is all in one column. Averaging 20 columns over 30,000 rows will hurt your brain.
    The integers represent times in milliseconds.

    Thanks all for your quick responses.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •