November 3, 2008 at 5:32 am
Hi,
I am working on a db with a structure similar to that in the picture. I tried to make this as simple as possible while still mimicking the problem I am trying to solve.
The Food table contains common attributes for any food. Fruit and Nut contain attributes specific to those food types.
Food can exist in a location. I want to have two stored procedures for retrieving food.
1) GetFoodByID - Should retrieve the relevant row in the Food table and by checking the FoodTypeID, the retrieve the relevant row in either the Fruit or Nut table.
2) GetFoodByLocationID - Should use a set of FoodIDs gotten from FoodLocation by locationID to retrieve all relevant rows in the Food table and by checking the FoodTypeIDs for the FoodIDs, it will then retrieve the relevant rows from both the Fruit and Nut tables.
Both are very similar, except that GetFoodByID retrieves food information for a single ID whereas the other retrieves food info for several.
I can use XML to create lists of one or more FoodID's then parse this into a table variable and use it to find the data I need. (I'd love 2008 table variable parameters)
If I do this, both procedures can make calls to other functions/stored procs that take in an XML parameter. So GetFoodByID would create an XML parameter with a list of FoodIDs, then send that parameter to a GetFood function/stored proc and a GetFruit or GetNut function/stored proc. Now I can reuse GetFood, GetFruit and GetNut in GetFoodByLocationID. Great!
QUESTION:
In both procedures, I still need to check the FruitTypeID(s) and then query the appropriate table(s). If another FruitType is added, I have to change GetFoodByID and GetFoodByLocationID to include the new case. Is there any way to avoid that?
I had a discussion with a colleague and he wondered if I could use delegates to call the appropriate function/stored proc from a CLR function/stored proc. Could I create a GetSpecific function/proc that used a delegate to execute GetFruit, GetNut or some new case as needed. Is this possible?
Thank you in advance.
-Peter
November 5, 2008 at 4:10 pm
In both procedures, I still need to check the FruitTypeID(s) and then query the appropriate table(s). If another FruitType is added, I have to change GetFoodByID and GetFoodByLocationID to include the new case. Is there any way to avoid that?
There is but it will be at the expense of the robust nature of your design. You have a good design with a "main type" (Food) and "sub type" (Nut, Fruit) layout. This has many advantages, not the least of which is that it will closely match the OO design of application code. Another is that the subtypes are independent of each other. If you add "Meat" then, as you say, you make an entry in FoodType, create a Meat table and off you go. Any views and/or code designed to work with Nuts will not be affected at all. Same with Fruit. Application code will have to be added to work with Meat, but that is to be expected. I doubt you handle meat and fruit the same way.
You may be tempted to merge Fruit and Nut together. After all, they only differ by one attribute but that attribute is (or could be) the same datatype in both cases. But what would happen if you had to add Color to Fruit, and ORAC_rating sometime later, and an IsCitrus bit field sometime after that?
This is a sound design pattern. Stick with it.
I had a discussion with a colleague and he wondered if I could use delegates to call the appropriate function/stored proc from a CLR function/stored proc. Could I create a GetSpecific function/proc that used a delegate to execute GetFruit, GetNut or some new case as needed. Is this possible?
Possibly, but it will make your code extremely fragile to changes and a nightmare to maintain. In your code, you may have sections that handle only Nuts and some that handle only Fruits. When you show "by location" you will probably show "here's all the nuts in that location and now here's all the fruit in that location." If you add Meat later, the nut-only and fruit-only sections will be unaffected. The sections that must handle all foods will be affected but that is to be expected.
Don't try to get too fancy. It's not possible to write code that is completely immune to changes in the database. So write the code (and design the database) to isolate the changes as much as possible.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply