locked
database table design RRS feed

  • General discussion

  • hi all

       i am getting software asset from different client machine nearly 2000 client per day,i am getting data in text file and uploading in database.my file look like this first day.

    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Cisco WebEx Meetings
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe AIR
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe Flash Player 10 ActiveX
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20120612|Audacity 2.0
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20110310|AVI Media Player 1.0.1
    

    den second day , same file with additional column

    SOFT|GE-DT-0049|000FFE0E68AE||2012-08-16 12:59:45Z||Cisco WebEx Meetings
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe AIR
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Adobe Flash Player 10 ActiveX
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20120612|Audacity 2.0
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|20110310|AVI Media Player 1.0.1
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z|Babylon toolbar on IE
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||Belarc Advisor 8.2
    SOFT|GE-DT-0049|000FFE0E68AE|2012-08-16 12:59:45Z||CCleaner

    here no primary key, i dont know how to design my data table,

    first day i am inserting all the data in table, second day also inserting all data with additional rows(i means new rows).now duplication is there,already existing data is repeating in second day insertion.

    please help me out.

    Thursday, August 16, 2012 9:16 AM

All replies

  • Your table schema may have

    • columns to hold each individual data element (column) from import file,
    • a RecordInsertedDate column - can be used to store the time at which respective record is inserted and can be defaulted to GETDATE()
    • an optional RecordUpdatedDate column - if table is bound to undergo updates.
    • an ID column - may be withan IDENTITY property (or custom generated values) as you don't seem to have a specific column (or set of columns) that define a row uniquely.

    You could use single RecordUpdatedDate column alone and initially use it to store RecordInsertedDate and modify the same when UPDATEs happen to the respective row. This can be used if you don't care when the record is first inserted into the table.

    Now, you need to write your import logic based on whether or not you want to UPDATE the already existing data during the import of file on a particular date or INSERT them again as a new row on its own which would have a different RecordInsertedDate.

    Thanks!

    Thursday, August 16, 2012 12:43 PM
  • how do you load your data into the database?
    Monday, August 20, 2012 1:32 PM