Data Access Design Patterns

Question

Hi, Im looking for a pattern which I can use for parent-child relationships when inserting records into a database. As the child record needs the parent record to exist first, Im having to use nasty things like storing the session id then updating the foreign key after saving the parent record.

Are there any well know patterns to solve this problem. Regards Gareth

Answer 1

You seem to be either recording children before or at the same time as their parents, which is possible but kind of odd. Your data model suggests children know about their parents, therefore, the data suggest a parent to be inserted before its children. You may not achieve this in a single query since the foreign key you're talking about is referring to the same table (A.parent -> A.id).

Answer 2

It sounds like you are not using a framework like NHibernate or Hibernate and you are rolling your own which is fine, but you may want to look at an existing OR mapping framework if you have this option. If you are rolling your own and you need to do linked inserts then you would do these using transactions in your repository, so as an example:

public class ParentRepository
{
    public void Save(Parent parent)
    {
        using (TransactionScope scope = new TransactionScope())
        {
           //Add you database code here to insert to both tables in a transaction
        }
    }
}