Should there be one-to-one relationship between DAOs and tables?

Question

Should there be a one-to-one relationship between DAOs and tables? For example, should only one DAO communicate with a given table?

Our data layer is kind of flawed, e.g. we have a MAN table that stores both users and user groups. I'm not enthusiastic about mimicking that mess in my code: I would rather have one DAO for users and another one for groups. Besides, in case the entities ever get separated into two different tables, I wouldn't have to do a code overhaul.

However, I don't rule out that it's more important to maintain that one-to-one relationship than having a good separation of concerns in your app. Perhaps, it would be easier to follow and maintain. Also, let's be honest: they never do that kind of splitting in mature projects: it's expensive, it's risky, it's hard to explain to non-technical management people. The mess is here to stay forever.

So, what's your take on this?

Answer 1

Join queries are an integral part of using a typical SQL/relational database efficiently. If you apply a strict mandate the the UserDAO is only able to access the User table, you prohibit yourself from using any database feature that allows you to access more than one table as part of a single query. You might be able to work around this by generating a list of ID's from one query and passing the list of ID's to another query as part of IN clause:

...
WHERE id IN (?,?,?)
...

But ultimately you are restricting yourself from using the databases ability to plan queries and use indexes as appropriate, to efficiently retrieve data. For that reason I would reject the strictest form of the mandate (absolute 1-to-1 correlation).

I am not sure, if it makes sense to examine weaker forms of the statement - such as "there should be one primary table that corresponds to the name of the DAO", simply because we get into a world of corner cases and exceptions.

Instead I would suggest that we look the other way - at the domain model and say that if you have a domain model that defines Users perhaps there should be a DAO that handles all operation related to Users - such a DAO should also handle any persistence/query functions that the Users domain needs (access other joined/child tables).

In the specific case you sight (Users and Groups) it is not obvious what the answer is, although you typically administer users and groups as different entities, when evaluating permissions you may choose to to simplify/merge the concepts of users and groups, TL;DR it is necessary to take a more wholistic view of the the domain model to decide what makes sense from a persistence standpoint.

To make that a bit more practical, I would suggest splitting the DAO into two (UserDAO and GroupDAO) then ask the question, how much duplicated code do we have between these two Dao's? If it looks pretty clean - most code is only used for the specific domain then keep it. On the other hand if you find that you have a lot of almost identical code, ask the question - would merging the two Dao's into one Dao reduce the duplication?

---

To address @Flater's comment.

I typically start with the premise that a classes public methods form an implicit interface for that class, particularly if all the other methods are private and there aren't any special cases - such that a typically user of the class will need all the public methods.

TBF, this isn't a hill I am willing to die on, if someone wants to pull out an interface for a single class, it doesn't significantly change the maintenance burden.

That said there are cases when it makes sense to make the interface explicit (create a new interface and implement the interface in the class). Probably the most common such case would be to support mocking out the data layer during testing.

The case for having a single concrete DAO class implement two interfaces for two similar Domain classes is likely dependent on both the domain and choice of language/libraries, for example Java type erasure can sometimes force a hierarchy of DAO classes - an abstract class that is inherited by concrete DAO's for each domain.

Instead of being able to use the simple case of one concrete class and two interfaces.

Answer 2

The Data Access Object Pattern provides an abstraction for data access. The main purpose is to decouple persistence knowledge from the rest of your application by providing a uniform interface to perform data operations. While it might be common to have one class per table, you aren't breaking any laws or committing unforgivable sins by having two entities and two DAO objects mapped to the same table. A head-scratcher, for sure, but nobody clubbed a baby seal. It really comes down to the basics of object-oriented design: encapsulation, data hiding, and separation of concerns.

I see three options which might be viable:

1. Have one entity class for Users and Groups with one DAO.
2. Have separate entity classes for Users and Groups, and still use one DAO which has separate methods for each entity.
3. Have separate entity classes for Users and Groups, as well as separate DAO classes and interfaces.

Which one you choose will largely depend on the structure of the existing application. If I had to choose, I would go with #3. The User and Group entities might be coupled in the database for normalization reasons, but that doesn't mean your OO model needs to reflect that coupling. Having two different entities, two DAOs with two mappings to the same table allows each entity to evolve on its own.

Consider defining a common interface for User and Group to handle situations where you need to deal with either one without knowing the concrete implementation.

Whatever you choose, this pain is here to stay, so make it as pain-free as you can knowing that there will be some blood. You'll survive (even if you don't want to).

---

^{BTW, I completely understand how this might be a sub-optimal design, but you would need to crawl into the heads of the original designers of this system. This might have been the solution with the fewest drawbacks (as opposed to zero drawbacks).}

Answer 3

In your specific case I'd make 2 views on that table and have your DAOs use those views. That's what views are for (or rather one thing they can be used for), create a customised way to access the data based on fixed criteria. It'd also give you the flexibility to set access restrictions for each view separately so for example normal users can see both but only modify users, while power users can modify groups as well.

And if the table ever gets split up, you just change the views and the application stays the same.

Answer 4

I've referred to a repository instead of a DAO in this answer, but the distinction is moot for the purpose of this answer. The central focus here is on the distinction between the interface and implementation of whatever storage mechanism/pattern you're using.

I'll also point out that you didn't specify which language you're working with, but most popular languages (that I know of) specifically have interface and class constructs for this very purpose. I am assuming you are using such a language.

---

The main thing I want you to consider is the distinction between a class and its interface.

• The interface sits on the boundary between the persistence layer and its consumers (usually the application/domain layers)
• The class is a private implementation detail of that layer. It's also responsible for figuring out how to map that private implementation and have it integrate with the aforementioned interface

This leads to a two-fold answer when you refer to "a DAO", because your question seems to be built on the presumption of a 1-1 relationship of DAO interfaces and classes, which is not an inevitable fact.

The interface is designed based on the codebase's domain (I'm using domain as a vague term here that means "however the codebase wants to tackle these concepts"). If your domain logic has a separate model for a user and a group, then I would expect to see two separate interfaces, i.e. IUserRepository and IGroupRepository.

If you didn't do this, and you'd instead create a single IUsersAndGroupsRepository, then I'd say you're leaking your persistence implementation as part of this interface design, which is a leaky abstraction and something to be avoided.

However, that doesn't mean that you need two separate UserRepository and GroupRepository classes to implement these interfaces. One class is able to implement multiple interfaces.

Since your class is your private persistence implementation, it is the representation of your persistence technology, and it will be designed with that consideration in mind (i.e. the data store, not the domain). This can (but doesn't have to) mean that you can do a "one class per database table" approach if you so choose.

Before we get to that decision, I just want to point out that the ability to design the interfaces and the classes individually, for their own reasons, is one of the cornerstones of having a properly abstracted layer. How the persistence layer works internally and how it plays with other layers are two separate concepts that should be designed each for their own individual reasons, and only when those two concepts have been fleshed out should you then start thinking about how to get these two concepts to integrate with one another.

If you only build one concept, and then build the other as an integration piece, you're creating a leaky abstraction. Either you're forcing your persistence implementation to mirror the domain structure (which restricts you from a different persistence implementation even if it's better) or you're designing your domain-driven interface based on a persistence implementation detail (which causes your domain to depend on your persistence tech). Both of these are bad situations to be in.

So, we've established that we are going to design the persistence implementation based on its own private needs. We have a single database table, but two domain interfaces. So the main question here is whether we should have two classes or one to handle the implementation. When you drill down into it, the main question here is what the benefit is of merging the classes.

If your repositories are only a "bag of queries", with no reusability between the individual (public) repository methods, then there's not really a benefit to lumping all these methods into a single class. In that case, I suggest you keep them separate simply to keep each class as small and digestible as possible.

However, there might be optimizations behind the public methods. You might be able to leverage some reusable queries that apply to multiple interactions with the database table, or you might have some kind of caching mechanic based on already fetched data from this table, where sharing this between user methods and group methods can lead to further caching benefits.
This is the part where you need to find a justification, but if a justification exists, it is perfectly acceptable for you to create a single class that handles all interactions with this data table.

Just to address an unmentioned edge case: it's also possible to have two separate classes which share a common ancestor (either through inheritance or composition) which contains the reusable bits. But this is a more granular approach that I'm skipping in favor of keeping focus on the two main avenues that you should consider here.

Such a class would implement two separate interfaces which, to a domain consumer, look like they're two separate repositories. The fact that they're actually referring to the same underlying database table is a secret that no one outside of the persistence layer is aware of, and therefore cannot possibly form a dependency on.

IF you ever break this table up, you would then be able to do so by only needing to change your persistence implementation, without needing to impact your domain/application logic, and that is exactly why/how we develop clean abstractions between layers in our codebase.

Answer 5

A common case where there isnt a 1 to 1 relationship is where you have inheritance.

eg

table Animal
   id
   type
   numberOfLegs

table Bird
   id
   numberOfWings

and then in the code

class Bird : Animal
{
  id = 1
  numberOfLegs = 2
  numberOfWings = 2
}

Then when you save a Bird object both tables are populated. Maybe your Man table is the base class of User and Group? or was intended to be?

In any case, it's a common use case where the same table is used for multiple types.

Answer 6

There should be no relationship between a DAO and a table. The purpose of a DAO is to load some entity which makes sense to the code... and the existence of a database schema is an implementation detail for that DAO to encapsulate.

Because sometimes, an entity will use multiple tables, sometimes multiple entities will use the same underlying tables... and sometimes, you may well find that multiple entities share some subset of multiple tables too.

The latter certainly isn't ideal, but it's something you might encounter if you're working with a legacy schema... e.g. where the entities have been restructured, and tables which matched old entities don't match the new ones.

Answer 7

Looks a lot like database table inheritance

This pattern happens more often. It is database table inheritance. User and Group have some common info, and probably divert for other info. In database tables one could have a discriminator that determines the inheritance. Whether two or even three tables is a matter of architecturing.

In general it helps to have the inheritance also in the programming language: a base class. So for instance "roles" may be used for both groups and users.

So my answer would say there should be three DAO classes, maybe a commune repository. Look how table inheritance is dealt at other locations, to have the same handling. The database table could be altered to at least distinguish group, used and common fields, and have a discriminator field.