-2

In our environment, we often have to import CSV files into a database, where each line represents a record. Though there is no actual business use case for storing the original line numbers inside the record, I noticed that storing them helps for debugging or tracking back certain records.

So does it make sense to make it a general rule or convention for the team to store line/records numbers of an imported CSV file in a database?

In other words, should we always create an additional column to store the line/row number of a CSV?

Please see an example below: do you think it is a good idea to always include source_row_number column and the constraint csv_records_uk_row as a general rule of design?

   create table csv_records (
      surrogate_key integer primary key, -- auto-generated key
      csv_file_id integer not null -- foreign key
      source_row_number integer not null,
      field_1 data_type_1,
      field_2 data_type_2, 
   ... 
      field_n data_type_n
      constraint csv_records_uk_row unique (csv_file_id, source_row_number)
   )
  • @Doc Brown Please, see now. Is it not clear? For database developers the question should be clear enough. Or should I ask on stack overflow? It seems not very specific to any database / programming language. – Eugene Lycenok Sep 5 at 6:13
  • who is we"? team, yes. "why do you want to know this?" - because as a general rule and from experience I store the source line number as a natural key - I would like to hear other views - the source records number turns out to be helpful in investigations, "what problem do you want to solve?" - set a convention for the team / find design considerations from other developers. Not curiosity, it is important thing to avoid complications in the future. – Eugene Lycenok Sep 5 at 7:46
2

There is no general design rule for storing or not storing line or record numbers of a CSV file. You store them when you have a requirement for storing them (like you want to to store the file line-by-line and need to preserve the order of the lines). Otherwise, you don't.

(Note most relational databases do not guarantee to preserve the order in which records were inserted first, so if your requirement is to keep the order, you need to take care of this on your own).

Since you commented "source records number can be helpful in investigations" - that can be a very valid requirement, especially when the records don't have any other natural key. But it depends on the structure of actual data, if there is already another key available which can be used for this, and how often you really expect to encounter the situation where tracking back a record to its original source line is a helpful tool.

Introducing additional columns into DB scheme just for extra purposes like debugging is always a trade-off - it can be helpful, but it does not come for free - you need to fill and maintain the columns, and when the data is manipulated afterwards, you may have to implement extra logic to update these extra columns as well. But if the imported records just stay immutable in the DB once they got imported, the extra effort is usually not that high.

So instead of seeking for conventions for your team which can be followed blindly, better make it a convention to ask "do we need this really / what actual problem do we want to solve by, for example, introducing an extra column?" and "what is the cost/benefit relationship?"

  • The requirement is to track / find the source records easily. This seems natural to me. I've been always adding CSV line numbers and set it as a rule for myself. I am a bit surprised that the question was not clear and people do not do that. I guess the question is about: do you consider that so-called "best practice" ? The physical order of the records doesn't matter, it is about tracking the source. I hope to find more understanding. – Eugene Lycenok Sep 5 at 7:56
  • Ok. Good answer and the view has been taken. Only the physical order is not relevant. I would up-vote the answer if I could. I am a bit frustrated by my question having been down-voted. The question is a bit abstract. Yes. But I think the dilemma is quite common and worth thinking. – Eugene Lycenok Sep 5 at 8:06
  • @EugeneLycenok: I will try to edit your question in shape based on the information you provided. Maybe someone else will retract their downvotes. But I cannto guarantee the question will not get closed either as "too broad" or "too opinionated". – Doc Brown Sep 5 at 8:07
  • Yep. The question seems clearer and well-structured now. – Eugene Lycenok Sep 5 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.