most urgent help needed making a business layer


I'm totally stuck. Please help :)

I am creating a query that returns a set of populated business objects. Lets take the scenario that customers can make orders, orders can contain products and there is a link table between orders and products called orderdetails.

In my business objects, a customer object will have an array of order objects, the orderdetails table will have an array of orders objects and products objects.

In essence, I need to return a set of finite set of customer objects in which all of the related objects are populated.

I have a stored procedure that returns me the relevant data for for each table/set of objects. I can populate the objects with their basic atomic attributes. The problem is setting up the relationships and setting up the arrays of objects. I need to do it as quick as possible :)

This is the only way i could think of (and it's not very good!)

Create the set of customer objects and store in a hashtable in which the key is the primary key of the record
create order objects. as i create the order object, retrieve the relevant customer from the hashtable by its primary key
create products and store in a hashtable in which the key is the primary key of the record, then and store in a hashtable in which the key is the primary key of the record
create order details, as i create order detail object, get the relevant orders and products from hashtable

That seems a total nightmare

Please tell me there is a better way. Next time I will invest in an OR mapper but for now I;m stuck!

Thanks a lot
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming (in outline) a setup like this at the database end

Table Customers
   CustomerID - Primary Key

Table Orders
   OrderID - Primary Key
   CustomerID - Foreign Key

Table Products
   ProductID - Primary Key

Table OrderDetails
   OrderDetailID - PrimaryKey
   OrderID - Foreign Key
   ProductID - Foreign Key

a query on these lines

SELECT Customer.CustomerDetails, Order.OrderID, OrderDetail.Quantity, Product.ProductDescription
FROM Product INNER JOIN ((Customer INNER JOIN [Order] ON Customer.CustomerID = Order.CustomerID) INNER JOIN OrderDetail ON Order.OrderID = OrderDetail.OrderID) ON Product.ProductID = OrderDetail.ProductID
ORDER BY Customer.CustomerID, Order.OrderID;

would return the information for your customers in an order in which you could extract the data for your business objects without having to use hashtables.  If the CustomerDetails change we've finished with the last customer object instance and need to create a new instance.  If the OrderID changes within the same customer, we've finished with the last order object instance and need to create a new one.

As I say, it's in outline.  But it's how I'd go.

andiejeAuthor Commented:

I have already considered that option and dismissed it (though perhaps wrongly)

For example, I don't want the order class to simply have a field customerID, i want the order class to have a customer object.

This is ok for the orders table because there is only one customer. However, what do you do in the situation for orderdetails where an order or a product can feature in more than one orderdetails record. How do you know if you have already created the relevant order object, and if you have, where have you stored it and how do you retrieve it?

the other alternative is for the business objects not to store objects and simply store an integer to represent the primary key of the object they reference.

If i do that however, how do i do things like, get all the products in a order. I would have to store the products in a table and then look through them one by one everytime i need to access all the products in an order (which will be a lot of times)

Any ideas?
I'm obviously a bit confused between the order object and the orderdetails object.  On my scenario - where orderdetails would be one line of a multi-line order - there wouldn't be a situation "where an order [could] feature in more than one orderdetails record".  It would be the orderdetail that featured in an order and it could only feature in one order.

Reading your latest post (and re-reading your first post) I see that that is not your scenario.  Indeed I still don't think I've fully understood your class structure and relationships.  I can't get my head round how an order can feature in more than one orderdetails record.  But I'm not sure that it matters.  I can understand how a product can feature in a number of other objects (whether that is properly order or orderdetails).  And I think we can talk concepts rather than specific object models.

Over-simplified, it seems to me that the essence of your approach is that you get all the data and create objects and then search through those objects (using, I agree, an efficient hash-table method of finding them) to complete those objects that contain arrays of other objects.  The essence of my approach is that the data is obtained in an order specifically designed to cut down the searching.

It's clear that my confusion about what are one to many relationships and what are many to many relationships means that my approach wouldn't work right down the line.  But does it have to be all or nothing?  Where there is a one to many relationship if we retrieve the data for the many side and the one side in a joined query and ordered on the one side we can create the one side instance and use it in building many instances of the many side.  So far as I can see that would work where the customer is the one side (to many orders, I think) and also where the product is the one side (although I'm not sure what other object is on the many side).  This will still leave a need to fill in the many-to-many links - for which a hash-table, or other searching, mechanism may be necessary.

But - in so far as your present approach is, indeed, a "nightmare" - I think this more ordered approach might reduce the "nightmare" elements.  A downside is, in retrieving all the data you need to build the one side in a joined query with the data for every instance of the many side, you would be bring over a lot of data that you don't need.  Until (e.g.) the customer changes, all the customer data repeated with each order is superfluous.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.