Best approach to connect master and child table while insert data in tables

Question

I need advice what is best approach to connect data between master and child table.

I have console application written in C# which scrape data from web, process data and insert them in table.

Master table with fields Id, OrdinalNumber, StringDate and child table with fields Id, OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate.

Because I scrap data from site I don't have some unique identifier so I'm looking on Ordinal number (number from site) and StringDate(created like date but in string format ddMMyyyy I scrap site ever day once daily).

While I insert data in table it is done like this:

INSERT INTO MasterTable(OrdinalNumber, StringDate) VALUES (@OrdinalNumber, @StringDate)

and after that I insert data in child table:

INSERT INTO ChildTable(OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate) 
VALUES(@OrdinalNumber, @StringDate, @Name, @Amount, @StartDate, @EndDate)

Is this okay approach or better approach would be to get Id from master table when I insert data call SELECT SCOPE_IDENTITY() to get last inserted Id and put them in child table with this query:

INSERT INTO ChildTable(MasterId, Name, Amount, StartDate, EndDate) 
VALUES(@MasterId, @Name, @Amount, @StartDate, @EndDate)

My question is it better to stick with first approach or use SCOPE_IDENTITY() to get last ID and populate child data with it and save in database? I will insert approximately 10000 rows in Master table and 20000+ rows in child table.

I'm using dapper for inserting data in tables.

1.

IDbTransaction transaction = null;

try
{
    using (IDbConnection connection = new SqlConnection(DbConnectionString))
    {

        if (connection.State != ConnectionState.Open)
            connection.Open();

            transaction = connection.BeginTransaction();

            string sql = "INSERT INTO Master(OrdinalNumber, StringDate)" +
                         "VALUES(@OrdinalNumber, @StringDate)";

            connection.Execute(sql, master, transaction);

            string sql = "INSERT INTO Child(OrdinalNumber, StringDate, Name, Amount, StartDate, EndDate)" +
                         "VALUES(@OrdinalNumber, @StringDate, @Name, @Amount, @StartDate, @EndDate)";

            connection.Execute(sql, child, transaction);

            transaction.Commit();

        }
    }
    catch (Exception ex)
    {
        if (transaction != null)
        {
            transaction.Rollback();
        }
        throw ex;
    }
    finally
    {
        if (transaction != null)
            transaction.Dispose();
    }

2. IDbTransaction transaction = null;

   try { using (IDbConnection connection = new SqlConnection(DbConnectionString)) {

         if (connection.State != ConnectionState.Open)
             connection.Open();
   
             transaction = connection.BeginTransaction();
   
             foreach(var m in master)
             {
                 string sql = "INSERT INTO Master(OrdinalNumber, StringDate)" +
                          "VALUES(@OrdinalNumber, @StringDate)" +
                          "SELECT SCOPE_IDENTITY()";
   
                 var id = connection.ExecuteScalar<int>(sql, m, transaction);
   
                 foreach(var c in child.Where(d => d.OrdinalNumber == m.OrdinalNumber))
                 {
                     string sql = "INSERT INTO Child(MasterId, Name, Amount, StartDate, EndDate)" +
                                  "VALUES(@MasterId, @Name, @Amount, @StartDate, @EndDate)";
   
                     connection.Execute(sql, new {MasterId = id, c.Name, c.Amount, c.StartDate, c.EndDate}, transaction);
                 }
             }               
   
             transaction.Commit();
   
         }
     }
     catch (Exception ex)
     {
         if (transaction != null)
         {
             transaction.Rollback();
         }
         throw ex;
     }
     finally
     {
         if (transaction != null)
             transaction.Dispose();
     }
   

Answer 1

Unless you are generating GUIDs for ID's, it's probably better to allow the database to manage your ID's for you. That means retrieving SCOPE_IDENTITY() from the master table, and using that to populate your related records.

You haven't stated how you are obtaining an "Ordinal Number," but the usual way in order to avoid key duplication is to retrieve the highest numbered key from the master table using a query. In addition to being costly from a performance standpoint, this technique also has a race condition: someone else could add a record while you're trying to figure out what ID to use, and now your related records are tied to the wrong master record.

But SCOPE_IDENTITY() already does this work for you, without the race condition.

It's worth noting that, if you use Dapper.Contrib, it will manage these IDs for you automatically:

[Table("MyDatabase.dbo.MasterTable")]
public class MasterRecord
{
    [Key]
    int MasterID { get; set; } // Defined as Primary Key, Auto-Increment in database
    string Name { get; set; }
    int Amount { get; set; }
    DateTime StartDate { get; set; }
    DateTime EndDate { get; set; }
}

var myMasterRecord = new MasterRecord
{
    Name = "Aaron Aames",
    Amount = 6,
    StartDate = DateTime.Now;
    EndDate = StartDate.AddDays(7);
}

connection.Insert(myMasterRecord);  // myMasterRecord.MasterID now contains the new ID.