OO Design vs Database Design

Question

Suppose I am developing an application for a product distributor in C#.

The distributor does the following 3 types of transactions:

(1) Indent

(2) Sell

(3) Stock

I am designing my classes as follows:

public abstract class Transaction
{
}

public class Indent : Transaction
{
}

public class Sell : Transaction
{
}    

public class Stock : Transaction
{
}

Now if I want to save these three types of information in three separate tables then how should I design my DA layer?

Should I build separate DA classes like

(1) IndentDA
(2) SellDA
(3) StockDA

or a single class TransactionDA and perform CRUD operations by checking their types by using as/is operators?

Or what else can I do? Any suggestions?

Answer 1

Check out the Gang of 4 design patterns.

Answer 2

If you know that your data is going into three separate tables, then I would normally have three separate DA classes.

However, if your tables are pretty much exactly the same, then you could genericize the TransactionDA and simplify your data layer. I would only do this if you know that you will have high volume of transactions and are going to be separating out your tables into different files or something, otherwise I would probably just simplify things and combine it all.

Don't try to create a TransactionDA unless all your separate transaction types are extremely similar.

Answer 3

Firstly, if you created a single class TransactionDA and checked types inside the class to perform CRUD operations, you would be violating the Open/Closed Principle, so I would definitely not go down that path.

As for suggestions on how to accomplish building out your DAL, I would suggest following some blog posts on people much smarter than I on what they think about this topic.

Repository is the new Singleton

Repository is Dead: Long Live Repository

Night of the Living Repositories

The conversation continues, I believe, but that should get you started.

Answer 4

You can use dependency injection, create a DA class for each and have them all implement the same interface ITransactionDA with your CRUD operations.

public interface ITransactionDA
{
  void Read();
  void Update();
...
}

public class StockDA : ITransactionDA
{
  //implement interface methods
}

Stock stock = new Stock(new StockDA());

Answer 5

I would use entity subtypes here. Create one table for the transactions (and as an earlier poster said, perhaps a different term would be better) and store everything that's common in there. Then create one "sub-type" table for each specialization. These subtype tables should have the same primary key as the main table (the "strong" entity) and the fields that are unique to that particular specialization. Each subtype is related to the strong entity in a one-to-one manner, with optional participation on the subtype end and required participation on the strong entity end.

Then, to make querying easier, define a view that (outer) joins the strong entity with all of the entity subtypes so that you can easily "see" everything.

Here's a simple (and common) example of how to set this up:

create table Employee (
  eid        int primary key,
  first_name text,
  last_name  text not null
)

create table Salaried (
  eid             int primary key,
  annualSalaryUSD money not null
)

create table Hourly (
  eid             int primary key,
  hourlyRateUSD   money not null
)

Answer 6

I will do something like that

public abstract class DistributerTransaction
{
    DistributerDA dataaccess;
}
public class Indent : DistributerTransaction
{
}
public class Sell : DistributerTransaction
{
}
public class Stock : DistributerTransaction
{
}

public abstract class DistributerDA
{
   /*Read();
     Update();*/
}
public class IndentDA : DistributerDA
{
}
public class SellDA : DistributerDA
{
}
public class StockDA : DistributerDA
{
}

Answer 7

I would use an ORM such as NHibernate and use it's multi-table inheritance capabilities and then not have to worry about it myself.