2

I'm in the process of redesigning a portion of my ASP.NET MVC application. I'm currently using Entity Framework 6.1 code first approach.

I've been reading as of late that (Correct me if I'm wrong, I don't know much about DBs): Joins are expensive; we should keep our database normalized and with the least queries executed as possible. Entity Framework does a join each time we "virtual" a property and try to retrieve it (Eager/Lazy Loading). We should avoid an Entity-Attribute-Value approach (EAV), unless denormalization becomes desirable (Correct me).

Code First approach allows us to write the DB schema using C# OOP code (POCO). Which means that we should adhere to OOP's SOLID Principles (Correct me). The first one is Single Responsibility, which means a class should do only one thing (again, correct me if I'm wrong).

Now here comes the problem (this is the first time it happens to me). I have a class with around 30 properties. Before thinking in anti-EAV, I went and separated the model accordingly:

enter image description here

As you can see I have many virtual properties without the List<> type, this means that it is a one-to-one relationship. I have read in a previous stack-overflow post that a good rule of thumb is that one-to-one relationships should be avoided in favor of having them in a same table. That is, of course, if that one-to-one is not called from other tables. We prevent EAV and therefore, Joins.

Following SOLID principles, I have extracted some properties to external classes. They will get their own table.

So, the question will be, should I favor a fully normalized design over a OOP Approach when modeling in Entity Framework?

5
  • 3
    You said that "joins are expensive." Is this the only problem you're having? Because if it is, there are easier solutions than turning your architecture upside down. Apr 15, 2016 at 22:55
  • 1
    Also, for what it's worth, "normalization" is the relational database equivalent of the Single-Responsibility Principle. Normalization basically says "store each datum in one place, and one place only." Also known as "Single Source of Truth." Apr 15, 2016 at 22:57
  • @RobertHarvey: Thanks. I just want to go with the standardized approach.... and I think I've found my answer: There is something called Table Splitting in Entity Framework, which allows me to map different Entities to a same Table, which will work perfectly in my case because that's what I think I need! c-sharpcorner.com/UploadFile/ff2f08/…
    – Jose A
    Apr 16, 2016 at 0:00
  • 2
    Code first isn't the standardized approach. It's just an approach. Apr 16, 2016 at 0:47
  • I know this is old. I just wanted to comment back that one to one relationship is not EAV, which is what I was misunderstanding this time.
    – Jose A
    May 30, 2019 at 13:57

2 Answers 2

7

First, let's rectify a couple of misconceptions.

Joins are expensive

As compared to what? Of course, reading one flat table is "cheaper" than joining tables, but any mature RDBMS is highly optimized for executing joins because they are inevitably part of sound database designs. Joins over foreign key constraints (the most common ones) are especially optimized. And of course, proper indexing is indispensable.

we should keep our database normalized and with the least queries executed as possible

The way you pose this, it seems to be a consequence of preventing these "expensive" joins. The reverse is true. Normalization will always result in more tables and, hence, more joins to query the same data as from a denormalized data schema. (Well, to be fair to you, later on you say "We prevent EAV and therefore, Joins.").

We should avoid an Entity-Attribute-Value approach (EAV), unless denormalization becomes desirable

EAV is all but denormalization. I'm under the impression that you don't fully understand what EAV is.

In an EAV design, attributes of a relation (aka fields, or columns, of a database table) are taken out of a relation and stored as records in an Attributes tables. The values are stored in yet another table that has foreign keys to the Attributes table and an Entity table. A record in the Attributes tables expresses one fact: this is value X of attribute Y in entity Z.

So with EAV, when applied rigorously, if you want to know the start date, end date, and cost of a tournament, you'll have to query the PGTournament table and join to Attribute and Value (with a WHERE condition for the attributes). That's two joins instead of zero without EAV!

Nearly always, EAV is bad design. It's to be used when there's no alternative (for instance in lab applications where new analyses for samples can be invented every day -- a fixed set of fields in a Sample table won't suffice).

In your case, I don't see any reason to introduce EAV -- I don't even understand why you bring it up. I think it is because you confuse EAV with 1:1 associations. Read on.

Which means that we should adhere to OOP's SOLID Principles

The EF class model is part of a data access layer. It's not a domain model! At least, that's not its first responsibility to be that. The class model should be dedicated to data access. That means that there will be bidirectional relationships and Id properties, to mention two OOP anti-patterns. And the real OOP bummer: the classes tend to be highly anemic. Whenever the EF classes can be used as domain classes, this is a mere bonus.

virtual properties without the List<> type

Such properties are known as navigation properties because the "navigate" to other entities. Lists are collection navigation properties and entity-type properties (without the List<> type) are reference navigation properties. They don't have to be virtual. When they're virtual, EF may be able to lazily load the properties.

this means that it is a one-to-one relationship

Why? Reference navigation properties are often the "1" part of a 1:n association. I think most of your reference properties are like that. For example, GameGenre. I think there are many tournaments having the same GameGenre. It's a 1 (genre) to n (tournament) association, even if GameGenre doesn't have a Tournaments collection. Maybe only TournamentSettings and MainImage are actual 1:1 associations.

1:1 Associations distribute data belonging to one entity over multiple tables. There can be very good reasons to do that. One of them is to facilitate querying light-weight data without the heavy payload of some blob, like MainImage. Another one is separating sensitive data from public data. Or common data (often queried) from specialized data (queried sometimes), maybe your TournamentSettings.

Now, finally, your question:

should I favor a fully normalized design over a OOP Approach when modeling in Entity Framework?

You're comparing apples and oranges. Normalized design is database, OOP is class model. But if there is anything to favor, it's normalized design. A well-wrought database design is pivotal to any data-based application. Everything else follows. The EF class model will necessarily closely reflect the database structure. As I said above: it must be seen as a data access layer.

But whenever you model business logic, of course, try to do it as SOLID as possible. That means that sometimes you'll have to populate a specialized domain model out of the entities queried by EF, and sometimes the EF classes can be extended to encapsulate behavior and data (which is what OOP is all about).

2
  • 1
    Wow. That's why I usually behave myself and don't talk as I know much. Thanks for the explanation. I ended up mapping the EF models to a single table using the Fluent API. In regards of the 1:1 relationship, you're right. I thought that all the 1:1 relationships were bad design. I just found this: slideshare.net/billkarwin/sql-antipatterns-strike-back/… and they're used for vertical partitioning.
    – Jose A
    Aug 12, 2016 at 2:57
  • 1
    And definitely I did not know well what was EAV. The presentation has just cleared everything for me.
    – Jose A
    Aug 12, 2016 at 2:58
2

Erg, this is why I hate EF.

Joins are not expensive if you index

Non list properties are not always 1-1 relationships, they can also be many to one.

Database normalisation is really good if you need to look at your database. (as well as the other reasons for it being good) EF can make and read a 'hard for humans' DB

Idealy for a nice readable, flexible db you want one table per class each with a primary key. Relationships represented by foreign keys or joining many to many non-class tables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.