| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2009-01-16 : 12:57:01
|
| what do you think of this design approach?For each definition , pre-defined and agreed upong by all branches, I want to be able to call a certain scalar function and pass it the @BranchID parm.@OfficialOperatingTime = SELECT int_OfficialOperatingTime(@BranchID)I am thinking of putting this function in a Definition table field. So based on whatever Definition is being calculated it know which function to call dynamically Definition TableDefinitionID DefinitionDescr DefinitionFormula-----------------------------------------------------------------1 Official Operating Time int_OfficialOperatingTimebut now question is how do you call this function or is this a weakness in my design approach?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 12:59:57
|
| nope. its not a good approach. can you elaborate on why you're trying to do like this? |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-16 : 13:07:48
|
ok the reason I want to do that is because user or some other process or some knuckle head db admin might the literal value "Official Operating Time" to "OOT". I want to avoid having "Official Operating Time" I want to instead use the ID..well I guess I will haev to use some literal somewhere....I guess I must figure out a way of locking down that Definition table...or have it in an XML field?  man! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 13:14:28
|
| why should db admin mess up with your tables data? i', afraid i cant understand what your scenario is |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-16 : 13:24:48
|
you sound like a dbadmin...i was exagerating, a hyperbole...sorry ..did not mean to offend db admins...but i fyou knew our sql setup it is not far fetched  so i am trying to do some calculations on the fly, for each , for each definitions...definitions table holds just the list of definitionsanother table branchdefinitions (with foreign key definitionID)details the definition values for each branch..maybe that is the problem here I should just combine these two tables....i think that is it! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 13:29:35
|
quote:
Originally posted by yosiasz
you sound like a dbadmin...i was exagerating, a hyperbole...sorry ..did not mean to offend db admins...but i fyou knew our sql setup it is not far fetched so i am trying to do some calculations on the fly, for each , for each definitions...definitions table holds just the list of definitionsanother table branchdefinitions (with foreign key definitionID)details the definition values for each branch..maybe that is the problem here I should just combine these two tables....i think that is it!
Not at all  I'm a SQL developer i was asking the reason as i cant imagine any db admins tampering a piece of code unless they are sure of business rules.will calculation formula also be dynamic? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-16 : 13:40:06
|
| no calculation formula will not be dynamic...but values fed to the formulas will be dynamic....for ex official operating time can change depending on the shift structure and breaks structure...one day government health services might say visakh16 needs more tea and cookies break time ...values have changed so will the OOT...formula stays as is underlying values though change all the time...but we also have threshhold boundaries...for example we will not accept visakh16 on 2 hours tea breaks... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 13:43:00
|
| but still what's the reqmnt which tempts you to store the function info itself as column value? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-16 : 14:06:08
|
ok I repent there are no requirements....it is Friday and I just felt like doing something wacky...so I have abandoned this design approach...i am going to go with this approachINSERT INTO VsyBranchDefinitionsSELECT dbo.int_OfficialOperatingTime(@BranchID) 'Official Operating Time'....etc |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-16 : 14:27:08
|
| ok this is what I am doing ...looks really oogly SELECT BranchID ,'Official Operating Time' AS DefinitionDescr ,dbo.int_OfficialOperatingTime(BranchID) ,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AS DefinitionDate ,GETDATE() ,'SSIS Package' FROM dbo.syBranches AS sb UNION SELECT BranchID ,'Scheduled Dwon Time' AS DefinitionDescr ,dbo.int_ScheduledDownTime(BranchID) ,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AS DefinitionDate ,GETDATE() ,'SSIS Package' FROM dbo.syBranches AS sb ORDER BY DefinitionDescr, DefinitionDate |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 23:35:26
|
| ok. so you're trying to create records with same values for other fields with only definition description varying? |
|
|
|
|
dynamic function call