StrataFrame Forum

Multiple Foriegn Keys

http://forum.strataframe.net/Topic2912.aspx

By Kevin Lingofelter - 9/19/2006

Hello,

I have a table with multiple foriegn keys. I'll use an Order table as an example:

OrderID
OrderDate
CustomerID
OrderStatusID
PaymentTypeID

So, in addition to the Order BO (I'll call this primary BO), I'll also have Customer, OrderStatus and PaymentType BOs (I'll call these secondary BOs).

Currently, this is what I'm doing to implement this:

In the primary BO, I have private instances of the secondary BOs, which are filled during the primary BO's instantiation. The primary BO has 5 custom properties:

CustomerCompany
CustomerFirstName
CustomerLastName
OrderStatus
PaymentType

In the get/set methods, I navigate to the proper secondary BO records and return/set the value of the results. 

Is this the best way to handle this?

Thanks!

Kevin

By Trent L. Taylor - 9/19/2006

Well, this will work, but it creates a bit of overhead.  Generally what I do is create scalar methods in the Foreign Key tables that will return just the value that I need.  Then in the custom property I will call that scalar method.  Instead of create privates for all of the BOs, which is actually fine, you can also implement the Using operator.  For example, we have a BO that houses all of our Countries and another for States (or provinces).  Each place that has a state or country field is just a PK (foreign key field). 

So let's just take the country BO.  First I create a scalar method in the Country BO that will return just the country name:

Public FUnction GetCountryName(Byval CountryPK As Integer) As System.String
'-- Establish Locals
Dim loCommand As New SqlCommand()

'-- Build the command text
loCommand.CommandText = "SELECT ct_Name FROM Country WHERE ct_pk = @ct_pk"

'-- Create the parms
loCommand.Parameters.Add("@ct_pk", Int)

'-- Set the parms
loCommand.Parameters("@ct_pk").Value = CountryPK

'-- Return the results
Return CType(Me.ExecuteScalar(loCommand), System.String)

End Function

Now that that is written, in the BO that has a foriegn key field, you can create a custom property and add some code that looks like this:

Public Readonly Property MyCountry As System.String
    Get
         Using Country As new CountryBO
              Return Country.GetCountryName(Me.my_country_pk)
         End Using
    End Get
End Property

Let me know if this doesn;t make any sense. Smile

By Kevin Lingofelter - 9/19/2006

I thought about doing something like this before, but if I am displaying the orders in a grid, wouldn't there be a database hit for each FK field for each order record?
By Trent L. Taylor - 9/19/2006

Yes, but scalar overhead is practically nothing.  If you actually create a SELECT command it will create more overhead.  If you are talking about thousands of records then you can create a BO and populate it first, but in my experience it usually ends up being faster using scalar methods.

If you put a packet sniffer on the network adapter that talks to the SQL Server and see the traffic, you will see that there is no additional formatting on the scalar method.  However, if you create a SELECT statement, it will create much more network traffic.

This is really the purpose of scalar methods.  To grab just a piece of data extremely fast.

By Kevin Lingofelter - 9/19/2006

Excellent! Thanks for the tips!

Kevin

By Trent L. Taylor - 9/19/2006

No problem.  Glad to help! Smile