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) )