Should I have one class for every database I use?

First, let me explain what I am doing. I need to take an order, which is split up into different databases, and print out this very large order. What I need from the orders is about 100 or so columns from different databases. The way I was doing in was querying with a join and assigning all of the column values to a variable in my one large Order class. This has started to become troublesome. I am wondering of instead of having one class that is comprised of 100 or so members that make up the order. Should I have just one class for every database I use, and then work with that?

Let me add to this. Basically, is it better to map you objects to the original database tables, or the result set. Because I have my object mapped to the result set and not the individual tables.

From stackoverflow

• It really just sounds like your preference to me. How would you prefer to work with it? Would it be easier for you to work with it as separate C# objects, or would it be easier for you to work with it as several SQL tables?

  jumbojs : I thought it would be easier the way I had it but it seems to be troublesome now because of the way some of the database relationships are designed.

• why not just load the data from individual db's inidividuallly?

  For example, your constructor for the 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
  }
  

  it makes it easier to maintain the sql which touches the individual DB's and you won't have a dozen different classes out there for each DB.

  Another Alternative is to have a Stored Procedure which returns multiple result sets which you can access via a DataSet in your code.

  Or you could make your join easier to deal with and maintain by turning it into a VIEW in one of your databases.

  One thing you really need to think about here is maintenance. How easy is it for you to maintain the code after you've not read it for six months, or even how easy will it be for some other developer to maintain the code with no prior knowledge of it. Pick the paradigm which you believe will be easiest to maintain and then code it that way

• I would recommend an object-oriented solution to this. Presumably your database is designed with tables that represent logical groupings of data. Each of these tables can likely be mapped onto a class in your system, although in some cases, it may be more than one table that makes up an object or there might be multiple classes that a table maps onto using subclassing. If you need to display data from multiple tables -- say a list of orders with some data from the customer associated with the order -- then you can either use views, joins, or stored procedures to construct an object of a view class that represents the selected data in the view/join/sp.

  Essentially what I am describing is an N-tier data architecture where you have a low-level data access layer that deals with data from a SQL orientation -- tables, views, stored procedures. Above this may be a generic object layer that deals with generic data objects and interfaces with the data access layer to store/retrieve objects from the database. Finally, above this you have a strongly-typed business object layer where your application works with classes that semantically linked to your application -- orders, customers, invoices, etc. There are many different patterns for implementing this type of general architecture and you should investigate several to see which fits your application needs the best. You might want to directly use an object-relational mapping like LINQ or nHibernate or you might want to layer a repository on top of an ORM.

  Personally, I think that structuring your application to deal with objects within the context of your domain, rather than simply as table data, will improve your code. It should improve understandability and maintainability. You will be able to encapsulate behavior within your domain classes rather than have it spread throughout your application. Of course, this assumes that you follow good design practices, but using OO design will encourage this. Separating out the business and data logic from your display logic will also make your application much more testable, as will breaking down monolithic classes into smaller, more focused classes that are interrelated.

• I would consider creating a class to handle the data in each seperate data store.

  Then, I would consider using a pattern, something like a Facade to group those sub-systems together. Search the web on 'Design Patterns' and 'Facade' for more information. The facade can then account for any specific interaction between the data in the seperate data stores.

  To me, it is much easier to maintain code that is logically grouped, and having seperate classes for seperate data stores makes sense here for me.

  jumbojs : This is interesting.

• One elegant and simple approach to attacking this problem is the Active Record pattern:

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

  Of course, it may not be feasible in every scenario. It could also be integrated with other patterns, as implied in other answers. I'm one who believes you will face tradeoffs no matter what approach you choose. All the best!

  Ian Varley : Hadn't seen that one named before, and it's one I use all the time. Thanks!

• I'm going against the grain here, but I'd say that keeping this object mapped to the result set, and keeping the join in the database, might be a better idea.

  For the business logic, an "order" is usually a single cohesive concept (at least, that's how you started out framing it). Could the fact that it is mapped into multiple tables (or databases) be an artifact of how the data is captured? I would ask myself these questions before breaking it up:

  • Are there tangible benefits to composing the order out of other objects?
  • Do the attributes have different cardinality? I.e. are some per-order and others per-line-item?
  • Can you reuse existing code for the composed objects?
  • Are you enabling some other interaction that's easier to do with multiple objects?

  If you don't answer yes to any of those questions, I'd wager your code will be simpler and more readable if it deals with just the order as an atomic object, and lets the database hide the complexity of where it's coming from (you could even use a view for that).

  Sheer number of attributes isn't usually a reason to break up an interface. But, if the complexity (or size) of the order object itself is what's getting you down, you might try to simplify it internally to use some sort of generic accessor method, like:

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

  One other note: while performance should rarely be your starting concern in logical system design, most cases involving database table joins will perform better if the database does the join, because the DBMS can use indexes and other mechanisms to perform the join efficiently and avoid loading pages from disk that aren't needed. Maybe all your individual queries do that too, but typically that's something the database can do an order of magnitude more efficiently than business logic code. (Of course, if the join is across physical database boundaries, that benefit might be lost.)