Understanding object aggregation and databases

[JohnnyD]

Hi,

I have a situation in a program I'm writing where I'm not sure of the best way of coding a couple of objects and was hoping for a few pointers.

I am using VB.NET 2003, so F/wrk v1.1. My business is horticultural in nature and I'm trying to help streamline the business with a new order fulfillment app.

In my program, I have products. Some products don't have any component parts, but others are made up of a selection of plants.

For example: we sell a trowel, which obviously doesn't have any component parts. But we also sell a large vegetable garden, which is made up of, say, 20 rocket plants, 30 salad plants, 5 spring onions, etc....

I have the following tables in a SQL Server 2005 database:

Products:
ProdID - Primary Key (Integer)
Name - varchar(30)
etc...

Plants:
PlantID - Primary Key (Integer)
Variety - varchar(30)
etc...

ProductContents:
ProdID - FK to Product Table (also part of the composite Primary Key of ProductContents table)
PlantID - FK to Plant Table (also part of the composite Primary Key of ProductContents table)
Quantity - smallint

I believe the ProductContents table is technically referred to as a 'join table'?

I have written the classes in VB.NET which load, save, edit the Products and the Plants. I also have Manager classes which store collections of these objects (ProductMgr, PlantMgr).

I have dealt with situations before with join tables where there are only two columns which are both FKs and both make up a composite PK (ie - to express a one-to-many relationship). This is pretty straightforward (using my product/plant tables as an example) because when I load an individual Product from the database, I also run a second SELECT query which returns any PlantIDs associated with that PlantID from ProductContents. I store that in an array of integers within the Product object. I can then load Plants from the PlantMgr object based on the PlantIDs stored in this array. I kind-of do the reverse when I save the Product to the database, I run a second INSERT query which updates the ProductContents table with the Plants associated with the Product.

Where I'm getting stuck is with the inclusion of the extra 'Quantity' column. I can no longer store the PlantIDs in an array of Integers because I also need to store the Quantity associated with that Plant. I also can't use the PlantMgr object any more (to express which Plants are in each Product) because it also doesn't include any information about Quantity.

I was just wondering if there is a standard way of handling this kind of situation, where I have a 'join table' which also includes extra columns as well as the two columns that identify the join.

I can think of a couple of ways but I can see good things and bad things about both ways.

Any suggestions would be very much appreciated. Sorry if the post is a bit confusing!

Thanks,

john.

[TechGnome]

I would probably handle it differently. I'd have two classes, a Product class and a Plant class. The Plant class would also contain a Quantity property. Then I would have a Plants property in the Product class which is an array of Plant.

Two queries... one to get the ProductID(s) - I'm guessing that what ever you are doing for this so far will probably continue to work.

It's the extraction of the Plants where I see the change. Currently you're doing a select on the ProductContents table, looping through that and loading the Plants... we can do that all in one go, AND get the quantity to boot.

SELECT PC.Quantity, P.*
FROM ProductContents PC
INNER JOIN Plants P
  On PC.PlantID = P.PlantID
WHERE PC.ProdID = {fill this in here with your product ID}

Now, you can simply loop through those results, filling your Plant class... and include the Quantity as part of the results.

Makes sense?

-tg