How do you deal with Service methods on 2 very similar sql tables

Question

I have 2 sql tables which are very similar. Only the Foreign Key is different for each table.

TemplateUnit table:

Id (PK)
ParentId
Name
TemplateId (FK)

TestplanUnit table:

Id (PK)
ParentId
Name
TestplanId (FK)

When I go for 2 tables which has the nearly same content - just the FK is different - do you really create duplicates of your CRUD methods in your service and dataprovider (using ado.net pure) ?

How would improve the service so only one kind of Get/Add/Update/Delete methods is used in the service and dataprovider class? I also do not want to make duplicate unit tests...

UPDATE:

This is my solution so far:

public class Unit
    {
        public string Name { get; set; }
        public int Id { get; set; }
        public Nullable<int> ParentId { get; set; }
        public int TemplateId { get; set; }      
        public bool IsLazy { get; set; }         
    }



public class UnitDTO
    {
        public UnitDTO(UnitMode mode)
        {
            switch (mode)
            {
                case UnitMode.Template:
                    this.ForeinKeyName = "TemplateId";
                    this.TableName = "TemplateUnit";
                    break;
                case UnitMode.Testplan:
                    this.ForeinKeyName = "TestplanId";
                    this.TableName = "TestplanUnit";
                    break;
            }

            UnitBO = new Unit();
        }

        public string TableName { get; private set; }        
        public string ForeinKeyName { get; private set; }
        public Unit UnitBO { get; private set; }
    }

    public enum UnitMode
    {
        Template = 0,
        Testplan = 1,
    }

My Get/Add/Delete methods in BLL and DAL get a UnitDTO object with all information needed.

Well one disadvantage could be - if this project would be done in a team - that you have to know which variable is used/needed in the DAL when you create the UnitDTO and pass it to the BLL for each CRUD method.

What do you think?

score 0 · Answer 1 · answered Jun 22 '12 at 00:19

I think that it would be better if you explicitly specify your type like the following steps.

public enum TableTypeEnum
{
    Template =0,
    TestPlan =1
}

public abstract class UnitBase
{   
    public int Id { get; set; }
    public Nullable<int> ParentId { get; set; }
    public string Name { get; set; }

    public TableTypeEnum TableType { get; private set; }


    protected UnitBase(TableTypeEnum  type)
    {
        TableType = type;
    }
}

public class TemplateUnit:UnitBase
{
    public int TemplateForeignKeyId { get; set; }
    public TemplateUnit() : base(TableTypeEnum.Template)
    {}
}

public class TestPlanUnit:UnitBase
{
    public Guid TestplanForeignKeyId { get; set; }
    public TestPlanUnit():base(TableTypeEnum.TestPlan)
    {}
}

and the DAL class may be like this

public class  DAL
    {
        public void Insert(UnitBase unit)
        {
            switch (unit.TableType)
            {
                case  TableTypeEnum.Template:
                    //insert into the template table
                    break;
                case TableTypeEnum.TestPlan:
                     //insert into the testplan table
                    break;
            }
        }

    }

By doing that way, when others people call your code, they know exactly which type of unit they are working with, and you can avoid duplicating your code. Hope this help.

You changed the DAL. So my BLL (UnitService) has still all the same methods for 2 different tables. The units of a template will always be the same as the unit of a testplan, because the testplan unit is a copy/backup of the templates unit. So the call from an asp.net mvc controller class would be unitService.Insert(unit) ? — Elisabeth, Jun 22 '12 at 16:44

score 0 · Accepted Answer · answered Jun 22 '12 at 07:55

Ok, I'm going to suggest you not combine the CRUD operations. Why can the Unit be stored in two tables? There must be some kind off rule in your domain that determines which table to store it in? This "rule" is an indication that your Unit can have more than one meaning/definition/specification, however slight it may be. The moment one of these specifications change (maybe an additional column etc.), you will be left with one set of CRUD operations that will get muddied by conditional statements, and this can get complicated.

If there's a fundamental difference between the two units, I would go as far as saying create separate business objects, with their own rules. Keep your design pure, keep it separate. Yes, it's more code but it's simpler.

its not more code it will always be duplicate code. If I change one testplanUnitService I have to change the other too. Both tables save the same data the difference is just the foreign key. The one unit table stores the units of the template. The testplans make a copy of all units of a template but stored in the other unit table because of the other foreign key. — Elisabeth, Jun 22 '12 at 16:09

How do you deal with Service methods on 2 very similar sql tables

2 Answers2