Data Access Layer for records with foreign keys


Web tier: servlets, JSP, Web frameworks: Data Access Layer for records with foreign keys

  1. Data Access Layer for records with foreign keys (7 messages)

    What are the best practices in creating a data access layer for a record with a foreign key? I know of three options, each with a significant tradeoff:

    A) Retrieve a business object for the record, with a field corresponding to the foreign key. When a field of the foreign record is needed (eg: Invoice.GetCustomer().GetName()), use a find by foreign key method to retrieve the foreign record. (Regular SQL SELECT without a JOIN)
    B) Retrieve a business object for the record, and a business object for each foreign record. Use pointers/references to associate the two.
    C) Retrieve a business object with denormalized fields corresponding to "cached" fields of the foreign record. (SQL JOIN)

    The disadvantage of A is clear: We are missing out on the power of SQL. Multiple trips will be required later on to the database. Finder methods will be overused.
    C breaks normalization and encapsulation. Still, it is lightweight (worth a lot in my book, at least for small projects).
    B is a good compromise, but gets very bulky.

    I feel like there should be a standard pattern for this, but I haven't been able to find any discussion, at least via Google.
    Am I missing something?

    Threaded Messages (7)

  2. The answer is yes.

    There is a lot of disconnect in the community about this very question. Some want to use fancy ORM tools (A&B), some want to stick with VO/DAO (A, maybe B), and some want to be quick-and-dirty (C).

    They're all right IMO. Though I'm not a big fan of ORM probably because I'm lazy and don't want to take the time to learn them.

    The right way depends on your situation. If you have a simple, quick-and-dirty project, consider C, but I would not create a business object for this, I would use recordsets unless there is some other compelling reason (DTO for example). If you have a large, complex project then look into the ORM tools or consider writing your own VO/DAO objects. I've even heard the Sun Studio can auto-generate CMP Entity EJBs based on your table definition.
    I feel like there should be a standard pattern for this, but I haven't been able to find any discussion, at least via Google.Am I missing something?

    I feel the same way, but there's too much disconnect here to arrive at a standard. There is work being done for this, however, EJB 3.0's data access model looks promising, hibernate has been generating a lot of noise, and I've even read comments from Don Box talking about the current disconnect between programming languages and databases saying that M$ is working on a solution.

    I personally am working on a tool to auto-generate VO/DAOs from the database definition. I've created a JSP style text generator and my first use of it is going to read the database definiton and output java code.
  3. Sun uses C[ Go to top ]


    Thanks for the info. It's interesting to note that Sun's Pet Store uses C, despite it being what you called "quick and dirty" - take a look.
  4. Sun uses C[ Go to top ]

    I beleive pet store was a sales pitch for using EJB, so they wanted to show you entity EJB. Entity EJB has largely been considered a failure or at least not quite right yet. All I'm saying is make sure you justify the cost of using CMP or copying your data from a recordset to a data object. There are many reasons to use a data object, but they don't always apply (on the flip side it may apply in the future).

    There are some tools out there for auto-generating the data access layer. You might want to check out
  5. Don't overpattern[ Go to top ]

    You have a good point. Too often we think "the more patterns, the more OO, the better", without realizing that the same development effort could be better invested in more mundane, practical improvements.

    Nevertheless, the purist in me wants to do things "right."
  6. OO is not always the best, anyway[ Go to top ]

    Nevertheless, the purist in me wants to do things "right."

    I've done some more research, and I now realize that OO is not always the "right thing," even from a purist's perspective. Unlike the network stack, for instance, where each layer encapsulates all the functionality that we need from the lower layer, OO and Relational are two parallel domains, each with its own advantages, and neither one (currently) encapsulates the other one. It's foolish to think that OO is universally superior. And, therefore, even from a purist perspective, even ignoring the cost of time, a data oriented focus is often superior. See The power of table-oriented programming for a great example.
  7. OO is not always the best, anyway[ Go to top ]

    I think what you've said here is the basis for the major disagreements people have about persistence tools.

    Some want pure OO without consideration of how it is persisted, but this is likely a mistake until OO persistence tools solve the problems that they are having.

    Others dismiss OO for this reason, however, this, IMO, is not the answer either. You need to use OO, but be careful about what object you are modeling. Don't model Customers, Orders, Etc. Model the database tables that persist customers, orders, etc.

    There are many benifits to doing this. Your persistence classes become auto-generatable. ORM tools can likely still be used in a manner that justifies their cost, it's a simple enough concept for trained monkeys to understand, etc.
  8. OO is not always the best, anyway[ Go to top ]

    I just took a look at the link you provided.

    This discusses a lot of the common problems with the persistence variations, but I'm not sure I like their approach to the solution. It seems like a variation of JDBC with some nice added features. It looks like it's trying to be like M$'s disconnected recordsets in ADO.NET. This can be a good choice for your persistence API, but I prefer VO/DAO style APIs and using JDBC if you need to take large recordsets into consideration.