computed column, or update trigger
Posted on 2011-09-21
UserComponents to ChannelTypeConfiguration is one to many; There are multiple records in ChannelTypeConfig with different ACTIVE ChannelTypes. I am creating ONE ChannelTypeConfiguration table, though there are mutliple channel types, and I need to store the active channelTypeIDs as comma separated values in UserComponents..ActiveChannels, like this:
UserName / Instance / ActiveChannels
JohnDoe 2 1,2,4,6
JaneDoe 1 4
JackDoe 1 1,4
ChannelTypeID / ChannelTypeName / ChannelConfiguration / Active
1 fullPacket abc 1
2 lastOrder def 1
3 DeltaScore ghi 0
4 Switch jkl 1
5 BookQty mno 0
6 lastOrder pqr 1
7 fullPacket stu 0
8 Switch jkl 0
This data is static and minimal, and it will be hit only at application startup (or restart/recovery). So I've got a little leg room with performance. I'm thinking a computed column, but both tables will change intermittently, so I don't want to persist it. I think I prefer the computed column over a trigger, but I wanted to see what your thoughts are regarding the integrity of this solution. And, the syntax on that computation. :-)
This is new table definition, completely changeable. We have distinct UserComponents that can have at least one active ChannelType association. Each channel type has different configuration attributes, and I need to allow great flexibility for addition/alteration of these configuration types, and other attributes, in the future. Having said that, I've come up with the following:
ChannelTypes: ChanneltypeID, ChannelTypeName, Active
one procedure: pass in username and instance, returns all active channeltype configuration records.
My thoughts with the ChannelTypeConfiguration table, is that this prevents me from needing one table for every ChannelType (ie FullPacket, LastOrder, Switch, etc.). We can just write them until we're blue in the face, adding new ChannelTypes and Configurations very easily, as needed.
I know my inquiry is for the computed column, but I'd love input regarding this new table definition, if any of the Experts have a little time.