I am developing a small ERP software. I'm wondering how to design entities as well as where to join them.

I'm just gonna start with an example, that makes it way easier.

Say I'm modelling a maintenance contract.

1 Contract
   1-n Machines
        1-n contacts
        0-n appointments
        1-n purchasing prices
        1-n selling prices

So I could go ahead in my domain layer, where I keep all the models and model a Contract class simply with its fields and a list of Machines.

A machine with a list of its fields and list of contacts, appointments and prices.

If I go with this approach, which certainly makes sense, what do I do when loading these classes from the database? If I write a method that simply loads a full contract from the database, I'm loading so much extra information that I might not need. Maybe I only need to display the head data from the contract.

If I load it only partially, I have to #1 write several methods to load the contracts from SQL, several queries. And #2 I always need to make sure that the right amount of properties are loaded, so its a lot of extra programming effort and room for mistakes.

What I am doing right now, is keeping a lot of the entities separate if they could logically stand on their own. (I'd go with the upper approach for example in an Order model since neither items nor an order really makes sense standalone)

Since I'm doing c# and MVVM I essentially have a Contract ViewModel, where I then have a list of Machine ViewModels and in there lists of Contact ViewModels etc.

This makes some sense to me, as I can freely mix entities together the way they need to be "viewed". The exact point of a viewmodel i think.

However, the class/method where I build those Contract ViewModels gets rather complicated and big. With a lot of lists, dictionaries grouping contacts by foreign keys etc. Making sure the models are in sync with the viewmodels.

And I'm not sure if I'm not misusing c# for the joining of entities when "thats SQLs job".

I am not used to seeing an architecture like this, so I'm wondering if I'm missing something?

  • ERP = Enterprise Resource Planning? An edit to define this would be appreciated. – candied_orange Dec 2 '19 at 21:45
  • I is always capitalized in the English language. See Entry 4 of 8 here. – Robert Harvey Dec 3 '19 at 16:02

"in my domain layer"

From reading your question, it sounds like your domain is a little anemic. This means - amongst other things - that you are trying to design this with a 1:1 mapping between your objects and their representation in the database.

But that probably isn't the best way of going about the implementation of your domain, because expecially in something like an ERP system, the model itself really depends on who is asking about the information.

  • For example - someone in the finance department probably doesn't care about individual machines in the contract. Most likely they care about the Contract itself, and so the Contract and its behaviour in this case is the central domain model. They're probably not so interested in the machines outside of their model number and pricing information, but they might be very interested in the list of contacts (who do I ring to get money from?).

  • On the other hand, someone who services the machines is probably not overly concerned with the details of the contract beyond some key information like the customer name, and is much more interested in the details of the individual machine they are servicing. And it's likely you don't even want them to know about pricing, since this might not be something that's relevant to their work. But they'll definitely want to know some details about the appointments for servicing, etc.

  • So a better way to organise this is to think about the personas that will be using this system, and then design models specifically for the use-cases they need to execute. Then design your database to domain mappings accordingly. Remember this is all coming from the same data source, you're just selectively mapping different parts of that data depending on who is asking about it.

    • You will probably end up with classes like "ContractFinancials" and "ContractServiceInfo" and so on, but you will also probably find that there is never a use-case that actually requires you to hydrate the entire hierarchy as it exists in the relational database.
  • Apart from making the development of the system easier to manage because you can focus in on specific cases, it will also make it easier to adapt the system to changes later on and allow you to extend specific parts of the model in very specific ways that you might need, without trying to generalise everything into a single relational hierarchy.

  • You'll also be in a much better position to optimise the loading of specific parts of the domain model as performance needs dictate. And here's another thing - over time you may well start to realise that the database design itself needs to change - perhaps you will decide that Machines go at the top of the hierarchy and the contract itself is just something owned by a group of machines, all depending on how the system is to be used and what works best for your specific use-cases.

  • I like the idea of making the models use-case based. Its similiar to what im doing right now, just im calling them viewmodels, but i think this approach is better. Especially since i implemented validation on the model level and i could run into contradictory rules. My domain probably is anemic, its prety much only models and their validation. – DFENS Dec 4 '19 at 16:03

It's sounds like you're running afoul of the Object relational impedance mismatch.

And im not sure if im not misusing c# for the joining of entities when "thats SQLs job".

Absolutely not. You should feel free to create data structures that have nothing to do with SQL.

What you have to do is preserve your in-variants so that if someone else throws SQL at the DB it still works. But you don't have to use SQL to do that.

Use SQL when it can do something useful for you that nothing else can. Don't use it just because you're talking to the DB.


Not everything is universally answerable

If i write a method that simply loads a full contract from the database, im loading so much extra information that i might not need. Maybe i only need to display the head data from the contract.

If i load it only partially, i have to #1 write several methods to load the contracts from SQL, several queries. And #2 i always need to make sure that the right amount of properties are loaded, so its a lot of extra programming effort and room for mistakes.

You've hit the nail on the head, there are pro's and cons to either choice. There is a balance to be struck.

If you only have one fetch methods that loads everything, this saves on having to create further methods (which increases code complexity), but it suffers from possibly fetching too much data.
Conversely, having multiple methods allows you to be more economical with your bandwidth, but it takes more time and complexity to implement.

Where you strike the balance is very contextual. My situation/opinion may be vastly different from yours, and not every application has the same requirements.

Development is an iterative process

The premise of your question is starting to muddy the lines between the stages of development.

  • First, you build something that works on a basic level
  • Then, you build something that works well, i.e. by removing/handling all the bugs and edge cases.
  • If optimization is needed, you optimize it. If.

There are two things your question seems to gloss over: if it works, it works, and never optimize prematurely.

What i am doing right now, is keeping a lot of the entities seperate if they could logically stand on their own.

If it works, it works. If your separate entity fetches work for you, great! If you had decided to fetch everything at once (even if you only need a subset), and it works for you, great!

There's nothing wrong with building something that works. Good practice improvements can always be refactored later. First focus on something that gets the job done, before you start polishing it.
As a developer, I always hack something together to confirm that my idea works, before I start applying good practice standards. This saves on time (in case my idea doesn't work) and allows me to keep the primary goal (getting the job done) in focus.

Just to make sure: I clean up my code before I commit. I'm not telling you to not follow good practice or clean coding standards. My point was that getting something to work should be the first step before you start optimizing it.

However, suppose that it turns out that the page with the full contract detail information is quite slow because it has to launch multiple separate queries.
Or, in case you already wrote the full details fetch method, you run into bandwidth or performance issues on pages that don't require the full details.

In either case, at this point and not before, you can make the judgment call on whether you additionally develop the full or limited fetch method (whichever one is missing), if the performance increase of using the right method at the right time is worth the effort of having to develop a second fetch method.

However, if there are no performance issues to be found, then there's clearly no need to develop the second fetch method. What you've built is good enough, so don't waste more time on optimizations that no one asked for.

Don't start optimizing for performance if you don't yet have something that works. It's (temporarily) better to have something that works imperfectly than it is to refuse to build something that's imperfect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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