Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: how to store multiple records feasibly?

    Am doing a simple application.
    Am using vb.net and sql server 2008.

    Functionality:

    For customers, I need to store the entered amount daily.
    Eg as follows:

    Name 01/01 02/01 03/01 04/01 ------ 17/04 18/04 (100 days from JAN to APRIL)

    Raja 100 100 - 100 - 100
    Priya 200 300 100 - - -
    Giri 100 -

    Daily it will come upto 300 records. So for 100 days 300*100 records.
    How to store the records feasibly in database?
    How to define table to store records for above example?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Store those data in a normalised way.
    Code:
    CREATE TABLE amount_per_day (
    	day_	date	NOT NULL,
    	person_id	INT	NOT NULL,
    	amount	INT		
    )
    To enter (part of) the data you gave in your example:
    Raja 100 100 - 100 - 100
    Priya 200 300 100 - - -
    Giri 100 -

    This would give:
    Code:
    DECLARE @person_id as INT
    DECLARE @day_ as date
    
    SET @day_ = '2014-01-01'
    
    SELECT @person_id = id FROM people WHERE name = 'Raja'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 100)
    
    SELECT @person_id = id FROM people WHERE name = 'Priya'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 200)
    
    SELECT @person_id = id FROM people WHERE name = 'Giri'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 100)
    
    SET @day_ = '2014-01-02'
    
    SELECT @person_id = id FROM people WHERE name = 'Raja'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 100)
    
    SELECT @person_id = id FROM people WHERE name = 'Priya'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 300)
    
    SELECT @person_id = id FROM people WHERE name = 'Giri'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, NULL)
    
    SET @day_ = '2014-01-03'
    
    SELECT @person_id = id FROM people WHERE name = 'Raja'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, NULL)
    
    SELECT @person_id = id FROM people WHERE name = 'Priya'
    INSERT INTO amount_per_day(day_, person_id, amount) VALUES(@day_, @person_id, 100)
    
    
    SET @day_ = '2014-01-03'
    ...
    You can define a de-normalised table with 102 columns (name + start date + 100 columns for the actual amounts). It may seem like a good idea now, but you will get a headache after a very short period.
    SQL is very good and efficient in processing normalised data. Processing de-normalised data in SQL is very very very (*) hard.

    (*) I have omitted a few "very"'s.
    Last edited by Wim; 08-11-14 at 12:02.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, don't store your data as 300 separate columns!
    Store the data in a subtable with one record per customer per day.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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