Should I have one class for each database that I use?

Question

First, let me explain what I'm doing. I need to make an order that is divided into different databases and print this very large order. What I need from orders is about 100 or so columns from different databases. The way I did was a join query and assignment of all column values ​​to a variable in my one large Order class. It started to bother. I'm curious, instead of having one class consisting of the 100 members that make up the order. Should I have only one class for each database used, and then work with it?

Let me add to that. Basically, is it better to map objects to source database tables or a set of results. Since I have an object mapped to a result set and not to individual tables.

Answer 1

I am going against grain here, but I would say that storing this object mapped to the result set and storing the connection in the database might be a better idea.

For business logic, "order" is usually a single cohesive concept (at least the way you started to create it). Could the fact that it maps to multiple tables (or databases) be an artifact of how data is captured? I would ask myself these questions before sorting out:

• Are there any tangible advantages for placing an order from other objects?
• Do attributes have different powers? That is, are there any orders and others for each item?
• Is it possible to reuse existing code for linked objects?
• Do you allow some other interaction that is easier to do with multiple objects?

If you do not answer “yes” to any of these questions, I would make your code simpler and more understandable if it only considered order as an atomic object and allowed the database to hide the complexity of where it is (you could even use it for this representation).

A simple number of attributes is usually not a reason for breaking the interface. But if the complexity (or size) of the order object itself is what let you down, you can try to simplify it inside to use some kind of common access method, for example:

private object GetOrderAttribute(string attributeName){ // use a data structure (like a hash table) to store and access internally } ... output("Quantity: " + GetOrderAttribute("quantity")); // etc. 

One more note: while performance should rarely be your main concern in the logical design of the system, most cases involving joining database tables will work better if the database makes a join because the DBMS can use indexes and other mechanisms to efficiently perform merging and do not load pages from disk that are not needed. Perhaps all your individual queries do this too, but usually this is what the database can do an order of magnitude more efficiently than the business logic code. (Of course, if the connection occurs across the boundaries of the physical database, this advantage may be lost.)

Answer 2

I would recommend an object oriented solution for this. Presumably your database is for tables representing logical groupings of data. Each of these tables can be mapped to a class on your system, although in some cases there may be more than one table that makes up the object, or there may be several classes that the table compares using a subclass. If you need to display data from several tables — say, a list of orders with some data from a customer related to an order — then you can use views, joins, or stored procedures to create a view class object that represents the selected data in the / join / sp view .

Essentially, I am describing an N-tier data architecture where you have a low-level access level that processes data from an SQL orientation — tables, views, stored procedures. Above this, there may be a common layer of an object that deals with common data objects and interfaces with a data access level for storing / retrieving objects from the database. Finally, on this you have a strongly typed level of a business object, where your application works with classes that are semantically related to your application - orders, customers, invoices, etc. There are many different templates for implementing this type of common architecture, and you should research a few to see what suits your application, the very best. You might want to use object-relational mapping directly, such as LINQ or nHibernate, or you might want to put the repository on top of ORM.

Personally, I believe that structuring your application to work with objects in the context of your domain, and not just as table data, will improve your code. This should improve comprehensibility and maintainability. You will be able to encapsulate behavior in your domains, rather than distributing it throughout the application. Of course, this assumes that you are following good design techniques, but using OO design will help. Separating your business logic and data with your mapping logic will also make your application more robust, as it breaks up monolithic classes into smaller, more focused classes that are interconnected.

Answer 3

Why not just load data from a separate db inidividuallly?

For example, your constructor for an Order object would look like this:

 Method New Order(orderId) { Get Database 1 Details Load Details into appropriate Variables Get Database 2 Details Load Details into appropriate Variables Get Database **N** Details Load Details into appropriate Variables } 

this simplifies sql support, which affects a separate database, and you will not have a dozen different classes for each database.

Another alternative is to have a stored procedure that returns multiple result sets that you can access through the DataSet in your code.

Or, you could simplify your communication and maintain it by turning it into HELP in one of your databases.

One thing you really need to think about is service. How easy is it for you to maintain the code after you have not read it for six months, or even how easy it will be for any other developer to maintain the code without knowing it. Choose a paradigm that you think will be easiest to support, and then write it that way

Answer 4

One elegant and simple approach to attack this problem is the Active Record template:

http://en.wikipedia.org/wiki/Active_record_pattern

Of course, this can be impossible in every scenario. It can also be integrated with other templates, as implied in other answers. I am the one who believes that you will face compromises no matter which approach you choose. All the best!

Answer 5

Actually, that sounds like your preference for me. How do you prefer to work with him? Will it be easier for you to work with it as separate C # objects, or will it be easier for you to work with it as with several SQL tables?

Answer 6

I would consider creating a class for processing data in each individual data warehouse.

Then I would consider using a template, something like Facade, to combine these subsystems. For more information, visit Design Templates and Facade. The facade can then take into account any specific interaction between data in separate data warehouses.

It is much easier for me to maintain code that is logically grouped, and having separate classes for individual data stores makes sense here for me.