title: Class Objects to Represent Relational Data
body: <p>I'm working on a project to gather data from an external system and bring it into our internal system. Most of our current system uses older technologies (classic ASP), and the project I'm working on requires some newer technology so I'm using VB.Net so I don't have much of an existing .Net foundation to build on.</p>
  
  <p>I have some pretty simple custom classes that I created for this purpose (ex. a Supplier class, Location class, and Product class). These classes were made to temporarily store data from the external system but in a structure that better fits our data structure. Consequently, they are pretty minimal and have only what's needed (ex. they aren't full object representations of each table with all columns).</p>
  
  <p>For the simple independent entities as mentioned above, this isn't a problem. However, where I'm running into problems is when I start trying to relate these independent entities. To accomplish this, I've been using additional classes for each class that inherit KeyedCollection. This keeps data access quick and efficient (ex. I can find things directly by key values without having to iterate through entire collections). For example, my Supplier class has a corresponding Products property of type ProductCollection (that inherits KeyedCollection). This way, Suppliers(150).Products(300) would refer to a supplier with a primary key of 150 and a product with a primary key of 300.</p>
  
  <p>My first problem is this: I want one "copy" of the product with a primary key of 300, and everything else to reference that "copy". This way, when I match up the primary key of the external data with the primary key of our internal data, I can update the Product object once and any/all references will reflect that update.</p>
  
  <p>My next problem is this: with some of the more complicated relations I'm getting to, I'm running into issues with not having the primary key of the link table's record. I need to have Suppliers(150).Locations(33).Products(300) to represent supplier 150 can go to location 33 to get product 300. In the database, this would mean there's a record in a Supplier table with a primary key of 150, and a record in the SupplierLocation table linking supplier 150 to location 33 (note this table has its own primary key, which is not available with the classes/structure I currently have in place - but let's say it's primary key is 1050), and a record in the SupplierLocationProduct table linking SupplierLocation 1050 to product 300 (again, this table has it's own primary key that is not currently known).</p>
  
  <p>What is the best way to accomplish the different things I need, given the scenario? I've looked at ORMs (ex. Entity Framework) but that seems like overkill for the couple of simple classes I need (however maybe doing this all manually is really what's overkill).</p>
  
  <p>Just to recap, I want one "copy" of each independent entity (Supplier, Location, Product), and anything using these entities would simply be a reference back to the original "copy". I also need the primary keys of link table records available. Performance is also an important factor here, but so is maintainability. I don't want to be wasteful (ex. using List.Contains() when I already have primary keys available) but at the same time, I'm not interested in a one-time savings of 100 milliseconds if it means writing my own core functionality (creating hashkeys, testing equality, etc.)
  <br /><br /><br />
  <strong>Additional Background Information</strong>
  <br />
  The purpose of this project is to obtain product availability and pricing information from the supplier, and import it into our internal system. A Location is a place where Products are available, and different Suppliers can obtain Products from the same (or different) Locations. In other words, a Location is not exclusive to a specific Supplier, and a Product is not exclusive to a specific Location or Supplier.</p>
  
  <p>In our internal system, all three of these entities are independent of each other, but are related using link tables. To further complicate things, the Supplier provides this information using two entities, a Ship To and a Product, where the Ship To represents a general source and destination location, and the product represents both the actual product and a more specific source location. For our purposes, we are only interested in the source location and actual product.</p>
  
  <p>Entity examples:</p>
  
  <ul>
  <li>Supplier provides Ship To "IN TO IN", Product "Widget A - Indianapolis, IN"; we import Supplier "IN", Location "INDY", Product "WID-A"</li>
  <li>Supplier provides Ship To "IN TO OH", Product "Widget B - Indianapolis, IN"; we import Supplier "IN", Location "INDY", Product "WID-B"</li>
  <li>Supplier provides Ship To "IN TO MI", Product "Widget B - Indianapolis, IN"; we ignore this (we already have Supplier "IN", Location "INDY", Product "WID-B")</li>
  <li>Supplier provides Ship To "IN TO MI", Product "Widget B - Gary, IN"; we import Supplier "IN", Location "GARY", Product "WID-B"</li>
  <li>Supplier provides Ship To "OH TO IN", Product "Widget A - Lima, OH"; we import Supplier "OH", Location "LIMA", Product "WID-A"</li>
  </ul>