Re: Help! Difficulty understanding DB -> Object mapping

Responding to Usenet.news.account...

My response assumes that your application does something beyond basic USER/CRUD pipeline processing. If that's all it does, then I am not sure an OO approach is the best one. A RAD solution or some pseudo-OO layered model infrastructure would probably be a better choice and you can ignore this response.

I have a simple system which allows orders to be created for customers.
I have studied various patterns and the like, and I have the following
data structure in the DB:

Order
=====
Order_ID
Customer_ID
Customer_Address_ID
...

OrderLineItem
=============
Order_ID
Product_ID
Qty
Price
Tax
TotalPrice

I have a number of questions. Is it normal to:

The first thing to do is forget about what the RDB looks like. That is only relevant to accessing persistence. Solve the customer's problem first and then worry about how objects in that solution will map into tables in the RDB.


1. Store the full chosen billing and shipping address with the Order,
though the addresses are stored in a separate table?

The RDB schema you suggest above violates 3NF. The customer address depends on the Customer identity, not the Order identity. If addresses are stored in a separate table, then the Customer_Address_ID should be in the Customer table, not the Order table.

However, this illustrates why one should solve the software problem first. One can conceive of contexts where Customer and Address are not separate objects. That is, if one processes one order at a time in a POS client, then the customer name and address could be unique to the Order and both are just attributes of that one entity. That's because the fact that the customer may have 14 different shipping addresses in 10 states doesn't matter; the POS system only "sees" one shipping address for the order.


2. Store the full product details with a Line Item? I currently do not
store the title of the product, just its id.

In the RDB there will undoubtedly be a separate table for Product. That's because things like product descriptions are unique to the Product, not the OrderLineItem. Some Product attributes, like description, would appear redundantly in OrderLineItems from different Orders while other Product attributes, like screw thread size, might be unique to a particular Product or class of products and wouldn't appear at all in some OrderLineItems.

OTOH, in the client application there might be good reason for having at least some of the Product information in OrderLineItem. For example, for display in a Shopping Cart one would want the details and they would be associated uniquely with the OrderLineItem. That's OK because the Shopping Cart abstraction <usually> constrains the problem so that there would be only one OrderLineItem per Product (i.e., multiples are handled solely through Quantity). IOW, {Order_ID, Product_ID} is equivalent to {Order_ID, Order_Item_ID}.


If I store all of these details in the DB structure, I must still load
them into objects.  My problem currently is in loading a customers
order.  I dont know where to join the product in the line items to the
actual product descriptions.  Do I do a DB join to get the ProductTitle
back, and put the product title in my OrderLineItem object? Or do I get
back the order line items, and do a secondary search for the product
based on its ID, load the product, and associate teh product to the
order obejct? I'm confused, and concerned about performances problem.

Deal with persistence in a subsystem or layer that understands the mapping rules (i.e., the mapping of object/attribute identity to table/field identity). That allows you to address performance problems in RDB access in that subsystem or layer so that it does not affect your problem solution logic.

The interface to the subsystem is designed around the problem solution's view of the data. So if the problem solution has a single object for Order with attributes for {customer_address, customer_city, etc.) it would request, say, saving that data via saveOrder (order_ID, customer_name, customer_address, ....) with all the attributes in the Order object. The persistence access subsystem needs to map that notion of Order into the actual tables in the RDB.

To do that the persistence access subsystem needs to know more about identity. For example, it needs a Customer_ID. One way to do that is via a table lookup in the persistence access subsystem between customer_name and customer_ID. Such a table could be created efficiently at startup with a single RDB query. That would save asking the RDB for the customer_ID given the customer_name just so that one can write a SQL query using customer_ID.

Alternatively, if the customer data usually comes from the RDB originally (e.g., the RDB is read to get get the default customer information to populate the form), then it might be more convenient for the Order object to store customer_ID when the data is read. In that case it is a handle just like a Window handle in a GUI -- just something one hands back to the persistence access subsystem to keep identity synchronized.


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl@xxxxxxxxxxxxxxxxx
Pathfinder Solutions  -- Put MDA to Work
http://www.pathfindermda.com
blog: http://pathfinderpeople.blogs.com/hslahman
(888)OOA-PATH



.

Relevant Pages

  • Re: Help! Difficulty understanding DB -> Object mapping
    ... > The first thing to do is forget about what the RDB looks like. ... > depends on the Customer identity, ... > Product, not the OrderLineItem. ... > To do that the persistence access subsystem needs to know more about ...
    (comp.object)
  • Re: Help! Difficulty understanding DB -> Object mapping
    ... Solve the customer's problem first and then worry about how objects in that solution will map into tables in the RDB. ... If addresses are stored in a separate table, then the Customer_Address_ID should be in the Customer table, not the Order table. ... Product, not the OrderLineItem. ... access subsystem to properly access the RDB to obtain the information, ...
    (comp.object)
  • Re: Relational database & OO
    ... It really doesn't matter to the time management problem solution whether the data is persisted in an RDB, an OODB, flat files, or clay tablets. ... So one could regard the DB Access subsystem I proposed as being a PSE from the perspective of the problem solution. ... I argue that the DB Access subsystem needs to deal with the particular persistence mechanisms that are at hand. ... Here is a basic example that you may find in a customer care & ...
    (comp.object)
  • Re: Queries and OO
    ... > this one of the reason why network databases was abandoned. ... the problem in hand and one instantiates them one at a time. ... index to an RDB table. ... First I can navigate to the customer object, ...
    (comp.object)