Designing DAL and BLL - Single/Multiple Data Repository for the Related Tables

Question

While designing a new multi-tier application, I am facing difficulty making a decision for my DAL and BLL layer design.

Suppose I have Employee information spread in multiple tables having both 1-1 and 1-Many relationship with master table. Few are listed below:

Employee (Master Table),
Employee_Contact_Detail,
Employee_Education,
Employee_Skill,
Employee_Experience

At DAL level, I have a generic data repository providing common functionality like GetAll, GetSingle, Add, Edit, Delete for each table.

Now, should I design my “Employee Data Repository” derived from my “Generic Data Repository” and add functions for all related tables listed above in a single class like GetEmployeePersonalDetail, GetEmployeeContactDetail, GetEmployeeEducation, AddEmployeePersonalDetail, EditEmployeePersonalDetail etc. In this way I would gain very less benefit for “Generic Data Repository”. The other way is that I create (and derive from Generic Repository) a separate data repository for each table and then create a single class at business logic layer for “Employee”.

EDIT

If I go for the "separate data repository for each table" option, at DAL level and then instead of "single business logic class" for “Employee”, if I create separate business logic classes, corresponding to each data repository, will it be indecent approach keeping in view the scenario?

Thank you very much for your guidance.

Answer 1

As mentioned in comment to your question, this is my opinion.

Problem:

If you already have base repository, why create specific repository for each table? Again, in BLL, you are creating separate Service class ("business logic class" as you mentioned) for each repository. This is all a repeat work and difficult to manage and understand.

Suggestion:

You have not mentioned what ORM (if any) you are using; so I assume your ORM provides necessary features to implement below architecture.
I suggest you close your DAL at generic repository. Do not write DAL classes for specific tables. All your Service classes will use generic DAL for basic CRUD functions. All extended functionality should be implemented in Service class itself.

About service classes, instead of creating one class for each Repository/Table is again repeat work. Better group your related multiple tables (Repository is out of question with reference to above paragraph) in one service.

For example, you create generic DAL that serves basic functionality for all tables. You create EmployeeService that covers all your employee related tables. You create AccountService that covers all your accounting related table. Similarly, LogService for all logging related activities. This makes easy to interact with services as you get all related members in one class. This also reduces repeat work.

Refer my this and this questions and their accepted answers.

I hope this explains your concern.

Edit 1

As mentioned in your comment, you us EF. It supports necessary features to implement what I suggested.

implementing extended functionality in BLL instead of DAL will overlap the tier roles

Yes; it does. Other side is also same. When you write DAL for each table, you are actually leaking Business Logic in DAL. In case any change in Business Logic, you need to modify DAL that does not make sense.
In my suggestion, even though layers are overlapping, concerns are still separate. DAL only handles CRUD. BLL handles all logic including Database logic.

Have you used same strategy in any of your projects?

Yes. In some projects earlier, I was creating separate DAL for each table instead of generic DAL. This caused huge repeat work. Even though projects were relatively small, maintenance overhead was more. Few months back, I started with relatively big project where I implemented what I suggested above. We can see the relief that gave to us now.