convert vba utility to object classes


I have a VBA utility that connects to a SQL database that I am changing to
I want to make proper use of the Object Orientated Programming and have some Questions about Classes.
I have gone through a couple of Tutorials.
I think I understand the Very Basics of OOP.
I also read somewhere that many programmers that have come from a Database (SQL) background fall into a trap of making a Class for each table (I Don't want to fall into that.)

OK - Some Examples

I have 3 sorts of Companies

I have come up with the Following class structure

Looks OK (Well I thought so :))
But from one part of the application (1/3 of the searching) we will be searching on the Combined Companies ie when the user searches then the application will need to search Debtors, Creditors and Prospects in one go.
What is the Best way to tackle this? One "Super" Class? or is there another way?

Products are a Class on their own but what about Suppliers Records?
Product A is one record but there are 6 different suppliers linked to Product A
Are all the Products and the Suppliers one Class?
Or a Separate Class for Products and the associated Supplier Info?

Also with the Products there are different Warehouses and Bin Locations - Are these separate Classes or Bundled in the One Product Class?

Note there are an Unlimited number of Suppliers and Bin Locations for each Product.

Any Help and Advise would be much Appreciated as I want to start it the best way.
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.

Carl TawnSystems and Integration DeveloperCommented:
This is quite a broad question, for which there is no single, definitive answer. Essentially what you are trying to do is model you data logically based on how the entities relate to, and interact with, each other. You will of course have to map your objects to database tables at some point, so you need to keep that in mind.

So, start with a Product. A product is a standalone thing, it exists in its own right independent of the Supplier/Bin/Warehouse, etc; so it makes sense for that to be a class. A Supplier is also an entity in its own right; so a Supplier class would also make sense.

Now, a Product has-a Supplier (or multiple suppliers), so a Product would normally have a way to reference the Supplier (or suppliers) that can supply it (this is containment in OO speak).

So skeleton Product and Supplier classes might look like:
Public Class Product

    Public Property ProductID As Integer
    Public Property Name As String
    Public Property Price As Decimal

    Public Property Suppliers As List(Of Supplier)

End Class

Public Class Supplier

    Public Property SupplierID As Integer
    Public Property Name As String

End Class

Open in new window

A Supplier could also reference a collection of Product objects in a similar way. How you design your classes, and what related data they directly reference, will depend on the usage scenarios for you application.

But the key is to model your data on how it is used in the real world, rather than how it is structured in the database.

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
p-platerAuthor Commented:
Thanks Carl

If I have a Product Class and a Supplier Class what happens in the Instance of a Product with 6 different suppliers - where would I get/put the supplier part numbers and Prices?

When I need to display all the suppliers for a Product do I make one call to the Product Class getproductdetails("ProductA") to get all the details then another call to the Product Class getproductsupplierdetails("ProductA")?

Then do I make 6 calls to the supplier class to get the supplier names or is it OK to access the Supplier class from the getproductsupplierdetails method in the Product class?
Jacques Bourgeois (James Burger)PresidentCommented:
To save memory, if you often handle long lists of Products, your approach might be the best thing because you can decide whether or not you need the supplierdetails.

But if you typically handle the products one by one, or in simple lists such as a PurchaseOrder, for the sake of simplicity in using your products, I would do the following.

In the Product class, you would have a property that is a collection of Suppliers. That way, a Product can have only one, but also as many Suppliers as your want.

You would have a ProductInfo class that holds the ProductID, PartNumber and Price for a given supplier, and a ProductInfoCollection to hold collections of these ProductID. This collection would have a method that would enable you to retrieve one of the products in the collection. It could be built around one of the Dictionary collections classes, where you can specify the index value of each element, making it easy to retrieve a specific product by using it as the index. This is what I assume in the code sample that is coming later.

In the Supplier class, you could have a property that is a ProductInfoCollection, holding all the products provided by that supplier.

That way, from the supplier you can get the products he provides with the data specific for that supplier, and from the product you can have the suppliers that provides it. Equivalent to a many to many relation in a database.

If built properly, all you need when you want to display all the suppliers for a Product is to create a new Instance of the Product. In its constructor, the Product class would itself call getproductsupplierdetails, that would build and return the SupplierInfoCollection for that customer.

To answer your question here is the code I would use to get the list of suppliers for a given Product:

Dim product As NewProduct (productID)
For Each supplier In product.Suppliers
    Dim info as ProductInfo = supplier.ProductInfos(productID)
    Debug.WriteLine(supplier.Name & " " & info.PartNumber & " " & info.Price)

If you often use long lists of products in which the supplier info is irrelevant, and also often use short list or individual Products where it is relevant, then you could have a constructor for your Product and ProductCollection class that determines whether or not to fetch the SupplierInfo. Some OOD purists would hit me for something like that, saying that if a property exists, it should be filled in whenever the data is available. But purists do not live in the real world, were stuff like memory and resources usage sometimes need to be taken into consideration when we design our classes

As you might have seen in our other thread, constructors play a great role in the way you deal with your classes.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Carl TawnSystems and Integration DeveloperCommented:
As you can probably tell from Jacques' comment, designing your data modal can be complicated and how you go about it depends largely on how you need to use the data.

Just to expand on Jacques' last comment about having a constructor to determine whether or not to load sub-data; you could also use an approach called "lazy loading", which basically has your object load nested data on demand, the first time something attempts to access it, rather than loading it at the outset.

If you were to use something like Entity Framework to model/map your data to the database, the Lazy Loading functionality is included by default.

If you wanted to roll your own, the basic pattern would be something like:
Public Class Product

    Public ReadOnly Property Suppliers As List(Of Supplier)
            If _suppliers Is Nothing Then
                '// Code to retrieve and load data from data layer
            End If

            Return _suppliers
        End Get
    End Property

    Private _suppliers As List(Of Supplier) = Nothing

End Class

Open in new window

p-platerAuthor Commented:

Now how should I do the Company classes?
3 Separate classes with one Parent Class - If so then how do I search the 3 classes at once?
Jacques Bourgeois (James Burger)PresidentCommented:
Ideally, you do not search in your own classes. You search in the database, and use the result of the search to build objects from your classes.

If you absolutely need to search in one of your classes, this assumes that you have build a collection of, lets say, Company objects. This can easily be done by inherithing from almost any of the collections that you find in the System.Collections namespace.

The one that is recommended most often by Microsoft for that purpose is System.Collections.ObjectModel.Collection(Of T). You could use it to create a CompanyCollection class (that is the convention for the name of a collection based on a custom class) by inheriting from System.Collections.ObjectModel.Collection(Of Company). Bang, you have it. It has all the features of a standard collection, geared to work with your Company class.

A lot of programmers put all their eggs in a recent addition to the framework, the ObservableCollection(Of T). I triggers events when items are added or removed form the collection, which makes it more interesting in some situations.

Another interesting one is the BindingList(Of T). It works well when data binding the collection to a grid or a ComboBox. Tim Van Wassenhove has nicely adapted it so that it is easier to sort on any column. I almost always use a personal variation of his technique when I need to sort one of my collections in memory.

You usually don't search into your classes when you have many classes working together.

When you want to search for something that involves many classes at the same time, it is almost always best to do the job at the database level. Launch the query or stored procedure that does the search, and use the result to build your objects and collections.

The idea is to try as much as possible to bring in memory only the data that you need, no more. Go back to the database when you need more.

As an example, I have a personal application that I use to handle a database that contains over 3000 recipes. I would be a fool to load all these recipes, with all their ingredients at once in memory. At today's count, that is 3168 recipe records, 23980 ingredient records, both joined with a few auxiliary reference tables such as types, associated recipe, etc... Most of it text, specially the steps to prepare each recipe. Think of all the resources it would take in memory.

So, I first get only the list of recipes names, filtered on a type whenever the user knows that he will be looking in only one type (desserts, italian, oriental...). This fills a ComboBox that uses AutoComplete features to help move through long lists. I also have 2 buttons that the user can use to move backward or forward in the list without having to open it each time.

Whenever the user select a different recipe in the ComboBox, or triggers a move for the previous or next recipe, I save the changes that could have been made in the currently displayed recipe, and then call the database twice. Once to get the details of the recipe into a Recipe object, and a second time to retrieve the ingredients for that recipe, that I accumulate in an IngredientCollection and display in a grid.

That way, except for the names, there is always only one recipe an rarely more than a dozen of ingredients in memory.

If the user wants to search, say for vegetarian recipes that take less than 20 minutes to prepare, I launch a query that gets me, once again, only the names of the corresponding recipes. I display them in the same selection ComboBox as before. So once again, the search is done in the database, not in the application itself, and I bring the recipes one by one on the screen.

Similar approach if I need to display the data in a grid. An application or its user rarely need all the data, they almost always need only a subset. So define what they need, and let the database do the search for you. Get only what they need. If they want to filter, go back to the database with a tighter search.

Naturally, each application has its needs, so that approach might not suit your application. My own applications are not all built like that. But this shows well how I try to minimize the amount of data in memory and delegate as much of the job as possible to the database. With it's indexes and optimized search algorithms, the database will always be better than you for searching data, specially when it needs to be joined between multiple tables or objects.
p-platerAuthor Commented:

So the way I understand it is

User needs to search on all three Company Types (Debtors, Creditors and Prospects)
Send the Parameters to a Stored Procedure which returns Results
Put these in a List of (Company)
After the user selects one of these THEN I can use the selected one to call one of the Sub Classes of the Selected Type i.e. Debtor Class

This will at first use the Company "Parent" Class to create the list as all the company types share these properties, then when I know which "Sub Class" is selected I can then call the Sub Class to get the properties that are specific to that Sub Class
Jacques Bourgeois (James Burger)PresidentCommented:
First, a little terminology. To understand the documentation, you need to know the correct words to used to define the elements of the code you write. In your situation, Company is the base class. Debtor, Creditor and Prospect are derived classes.

At this point, your are the one best equipped to understand what you should do, because you are the one who understands your needs.

But the way I understand it, you do not search on all three Company Types, and THEN I can use the selected one to call one of the Sub Classes

Why get them all to then work with only a subset? Go straight for the type of company that the users need to work with. Have something such as a series of RadioButtons where the user selects the type of company, and then retrieve only the type of company that he needs, in the class defined for that company.

One question to ask is whether you really need different classes for the types of companies. In the environments where I worked, we were OK with only a Company class. Do you really need different properties to make the differences between these?

In some environments, the classification between the 3 types was done manually, by setting a property called Type in the Company class. In order environments, we simply passed the desired type to the constructor of the CompanyCollection class. The constructor used different queries depending on the type. Prospects where companies that had never ordered, debtors and creditors were separated by whether their balance sheet was negative or positive.

There was no need to have special properties for each, the Company class had all we needed. No matter the type, all were handled by a collection of companies. The only thing that differentiated them in the code was the name that we used for our variables.

Don't fall in the trap that I see with almost everybody that starts working with classes. They all end up doing too much, creating a complex system where simple things do the job better.
p-platerAuthor Commented:
Thanks Very Much.
Top Advice
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.