sp advice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sp advice

Author

Topic

mg
Starting Member

4 Posts

Posted - 2006-04-29 : 08:07:06

hi,
i'm not sure if this is the appropriate forum to submit my question but i would appreciate some advice regarding the following:

i have a system which is used to give nutritional/health advice for a particular individual on the consumption of a particular food product, according to his medical conditions.
the way the database is currently designed is as follows:
health_info table :
some of the fields: username, medical_conditions ( comma-sperated string of medical condition IDs)
medical_conditons table :
some of the fields: cond_id, avoid_items ( comma-seperated string of ingredients to avoid )
food products table: prod_id, ingredients ( comma-seperated string of ingredients )

now to return advice for a person with username 'xyz' , consuming product 5, i have a stored procedure which executes in this manner:
i get the medical_conditions IDs (which are in the medical_constions field) for 'xyz' , and add into a temp table. i use a cursor loop through this table and for each such med condition ID , i do the following:
i split the ingredients for product 5 in a similar way into a temp table, and i split the avoid_items for the condition into a temp table too... i then get their intersection (which would be the table of ingredients which the product contains and which the user should avoid , according to this particular medical conditon).
this result is used to return a warning to the user.

now, the reason i have used these comma seperated fields, is to reduce the amount of storage space (of tables) since i would have a huge number of users each having different medical condtions, and a huge number of products each with their own ingredients.
however, i am in serious doubt as to whether this actually makes sense. i mean the procedure works fine, but i dont think that it's d most suitable way to handle such processing.

any ideas on how i should work? please don't hesitate to criticize :)

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 11:03:26

"however, i am in serious doubt as to whether this actually makes sense. i mean the procedure works fine, but i dont think that it's d most suitable way to handle such processing"

Well I wouldn't do it that way!

For your "comma-separated string of xxx" you should make a separate "child" table of your existing "parent" table.

Yes it will use more disk space, BUT it will find the appropriate data in milliseconds, whereas your method will have to plough through all the rows trying to find the stuff in the comma delimited columns. Once you have a decent number of patients that will become unworkable.

(This is all to do with NORMALISATION, which you might like to read-up on if you are not familiar with it)

Then instead of your CURSOR [Cursors are also slow, relative to "set based" methods] you can just JOIN all the tables to get your answer. Something like:


SELECT ... the columns you need ...
FROM   health_info AS HI
       JOIN user_medical_conditions AS UMC -- this is the Child of health_info
         ON UMC.username = HI.username
/** Optional table (if you need columns from [medical_conditions] in your SELECT)
       JOIN medical_conditions AS MC
         ON MC.cond_id = UMC.cond_id
**/
       JOIN medical_conditions_items AS MCI -- This is Child table of medical_conditions
         ON MCI.cond_id = UMC.cond_id
       JOIN food_products_ingredients AS FPI -- This is Child table of food_products
         ON FPI.ingredient_id = MCI.ingredient_id
/** Optional table (if you need columns from [food_products] in your SELECT)
       JOIN food_products AS FP
         ON FP.prod_id = MCI.prod_id
**/
WHERE  HI.username = 'xyz'
ORDER BY ... list of suitable columns ...

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-04-29 : 11:13:37

I just saw Kristens post after I wrote mine but I'll post it anyway

Just my opinion: Normalize the model
I believe that the pain of development along with the performance issues for every call to the database FAR outways the additional cost of disk space. In dollars and cents it will cost your company much more for development costs when you have do deal with cursors and parsing for every call then the cost of disk space (which is pretty darn cheap in comparison) With a normalized model your t-sql becomes straight SELECTs with normal JOINs...easy to code and the "user experience" will be much happier because they won't have to wait as long for their results :)

Be One with the Optimizer
TG

mg
Starting Member

4 Posts

Posted - 2006-04-29 : 11:39:28

thanks for the replies :} yep i'll take your advice and use child tables instead

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 11:40:48

Blimey, a convert - don't see many of those round here!

Let us know how you get on ...

Kristen

mg
Starting Member

4 Posts

Posted - 2006-04-30 : 14:16:25

Ok, so i've fixed all my tables and managed to get the list of ingredients which are found in the product and which the user should avoid according to the allergy:

SELECT PIng.ingredient as ingredient
INTO commonItems
FROM Subscribers.Pers_Allergies PA
JOIN Allergies.Allergies A
ON A.id = PA.allergy_id

JOIN Allergies.Allergy_AvoidItems as AAI
ON AAI.allergy_id = A.id

JOIN Products.Product_Ingredients as PIng
ON PIng.ingredient = AAI.ingredient
AND PIng.prod_id = @prod_id

WHERE PA.username = @username
ORDER BY A.[name]

now i need to use this commonItems table so that i will get the list of product names which are in the same food category (a field in Products table) as in the variable product (@prod_id) which do not contain the ingredients in commonItems. em i've been trying to think how i could do that in a simple enough manner...but i didnt have much success...any pointers pls?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-01 : 09:51:23

Well, we got you into this mess I guess we better help you get out of it :)

To answer your question, see if this does it. However, There may be an easier way (avoid building commonItems table) to get your answer in one step. Depends on what the actual question is though...


select	p.prod_id
	,p.product_name
from	products p
join	product_ingredients ping
		on ping.prod_id = p.prod_id
left join commonItems ci
		on ci.ingredient = ping.ingredient
where	ci.ingredient is null
and	p.food_Category = (select food_Category from products where prod_id = @prod_id)
group by p.prod_id

Be One with the Optimizer
TG

mg
Starting Member

4 Posts

Posted - 2006-05-03 : 13:16:59

hehe it's more likely that u got me OUT of a mess rather than into one :) thnaks for that..i was having some probs with it though cos it was returning the unrecommended product as well ...i got out of it using this:

SELECT P.[name]
FROM Products.Products P
WHERE P.id NOT IN ( SELECT Ping.prod_id
FROM Products.Product_Ingredients Ping
JOIN Allergies.Allergy_AvoidItems AAI
ON AAI.ingredient = Ping.ingredient

JOIN Subscribers.Pers_Allergies PA
ON PA.allergy_id = AAI.allergy_id
WHERE PA.username = @username )

AND (P.category = (SELECT category
FROM Products.Products
WHERE id = @product_id ))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources