Solved

computed column, or update trigger

Posted on 2011-09-21
32
268 Views
Last Modified: 2012-05-12
UserComponents:                 UserName,Instance,ActiveChannels
ChannelTypeConfiguration:  ChannelTypeID,ChannelTypeName,ChannelConfiguration,Active

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:

UserComponents -

UserName / Instance / ActiveChannels
JohnDoe      2                 1,2,4,6
JaneDoe      1                 4
JackDoe       1                 1,4


ChannelTypeConfiguration -

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.  :-)

NOTE:  
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:

UserComponents:                  UserName,Instance,ActiveChannels
ChannelTypeConfiguration:   ChannelTypeID,ChannelTypeName,ChannelConfiguration,Active
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.
0
Comment
Question by:dbaSQL
  • 19
  • 13
32 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 36573253
Possibly a function, with which to do that computed column?  Having problems doing it at CREATE, rather than the ALTER TABLE ADD newcolumn.  Any thoughts?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36573284
Still anxious for feeback on the computation, and the general design.  as for my last question, i figure i'll just put it in after the fact with the alter table, then i will see what SQL tells me to do, on the CREATE TABLE.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36573319
A few questions / observations come to mind:
1.) What is the connection between UserComponents and ChannelType / ChannelTypeConfiguration.
2.) Beyond the additional column, what is the difference between ChannelType and ChannelTypeConfiguration?
3.) If ChannelTypeName is finite set of options, you may want to consider moving this to a code / lookup table.
4.) If you will be pulling your information using a stored procedure, consider performing the comma separated values in there.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36573417
1) One to many.  Each UserComponent may have at least one ChannelType, and each ChannelType has multiple configuration values.
2) Each ChannelType has multiple, different configuration values, and this is ever-changing data. I did not want to create X number of different ChannelType tables, storing all of the configurations in separate tables.  So, we've got a small ChannelType table, simply referencing the distinct ChannelTypes, and we've got a ChannelTypeConfiguration table, which is generically defined, such that we can store all of the different configurations, for all of the different ChannelTypes, within a single table.
3) I understand what you're saying, but in my mind, that is pretty much what both ChannelType and ChannelTypeConfiguration are -- lookup tables.  And again, this is ever changing data.  We've got 12 different ChannelTypes now, each with a varying number of configurations.  Tomorrow maybe we'll add 2.  Next month, 20.  Ever changing.
4) Again, I understand, and the procedure will actually be parsing out the comma separated values, IF needed.  It is basically just a retrieval for application startup -- Pass username and instance, give me back a list of my active channelTypeIDs.  1,2,3,4 -- that is all that is needed.  I don't want multiple records in UserComponents, one for each ChannelTypeID.  Rather, I want one record in UserComponents, referencing the multiple ChannelTypeID values within ChannelTypeConfiguration, where Active = 1.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36574113
Going back to my first question what, within your database structure, tells you that JohnDoe has ActiveChannels 1,2,4,6, while JaneDoe only has ActiveChannel 4? If a computed column were to be used, your database structure would have to "drive" the calculation...i.e. you would need a relationship between your UserComponent table and your ChannelType table. For example, if you had parent - child tables and wanted a computed column on the parent to perform a sum on values in the child table, the child table would have to carry the primary key of the parent table.

One other thing to consider: If you only need this data at application start up, and you have the relationship established between UserComponent and ChannelType via a one-to-many or even a many-to-many join table, could you not just retrieve the values and avoid "joining" the values on the database side and then (presumably) splitting the values on the application side?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36574281
>>tells you that JohnDoe has ActiveChannels 1,2,4,6, while JaneDoe only has ActiveChannel 4? If a computed column were to be used, your database structure would have to "drive" the calculation...i.e. you would need a relationship between your UserComponent table and your ChannelType table.

Yes, UserComponents will have different ActiveChannels, because they use different ChannelTypeID's, with the associated ChannelTypeConfigurations.

As much as I would love for the one to many, it seems that I am wrong.  It would be a many to many.  One UserComponent can have multiple ChannelTypeIDs, and one ChannelTypeID will be associated with many UserComponents.

Possibly I include the ChannelTypeID in the UserComponent table, inserting multiple records for a UserComponent, when a UserName uses multiple ChannelTypeIDs.
Then in my procedure, given a UserComponent.username and UserComponent.instance, I join to the ChannelTypeConfiguration table by ChannelTypeID, and return all of the Active ChannelTypeConfigurations, for the given values.

crap.  i was really wanting to keep the entries in UserComponents more distinct than that.  maybe i'm just looking at it the wrong way
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36574452
Maybe consider adding a table that contains the IDs for UserComponents and ChannelTypes. This would allow you to have the many-to-many relationship you are looking for.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36574610
>>This would allow you to have the many-to-many relationship you are looking for.
I was looking for one-to-many.  But, as I said, it's completely changeable at this point.  Can you elaborate a bit?  Show me the table def you are proposing?
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36574972
The table layout would be the primary keys from both of your tables.
If the primary key for UserComponents is UserName and the primary key for ChannelType is ChannelTypeID, then the table definition would be two columns, one for UserName (with the same data type as in UserComponents) and one for ChannelTypeID (and similarly, with the same data type as in ChannelTypes). This would allow you to have a user have multiple channel types, and one channel type have multiple users.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36581790
I think this may work, but I am wondering if I can insert into this table based on the insert into my other two, larger tables?

unfortunately, that would be two distinct inserts, two different tables, to create the single insert into this table.  (with the primary keys from both other tables)

having said that, now i am wondering if a view of this format would suffice?  the key values would be created dynamically, simply upon insertion into both of the larger tables.

what do you think?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36581820
nope.  that won't work, because i still don't have the relationship between the two tables, UserComponents and ChannelTypes.

back to the trigger or update to my new table, to write pk values from the two larger tables collectively, into a single string/record in the new table

new table:   ChannelKeys
  UserName
  Instance
  ChannelType

First two attributes from UserComponents, third one from ChannelTypes

any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36586452
>>This would allow you to have a user have multiple channel types, and one channel type have multiple users.

I have a UserComponent table with UserID as PK
I have a ChannelType table with ChannelTypeID as PK
I have a third table, call it Keys, with two attributes; UserID,ChannelTypeID

ChannelTypeID is not in UserComponent table.
UserID is not in ChannelType table.  

Front end passes me the UserName and Instance (from UserComponents), and wants to see all ChannelType values for said user.
I will derive the UserID for the given @UserName and @Instance, and go to the Keys table JOIN ChannelType, to get all ChannelTypes for this UserID/ChannelTypeID.

I can even do the reverse... they pass me the ChannelTypeID, I go to the Keys table JOIN UserComponents, and get the UserName/Instances for this ChannelTypeID/UserID.

I see there are several different manipulations I can do here, by referencing the key table.

But -- how do I get the data into this table?  Inserts into UserComponents will be separate from inserts into ChannelType -- thus, the PK values are selectively generated.  What is the best way to populate my key table, dynamically, upon insertion into the two corresponding tables?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36586474
That sounded great, but I still don't think it is going to cut it.  I'm having a hard time establishing the data flow between entities, and of course, the population of my key table.  Any Expert ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36586986
INSERT dbo.Keys (UserID,ChannelTypeID)
SELECT uc.UserID,ct.ChannelTypeID
FROM dbo.UserComponents uc, dbo.ChannelTypes ct
GROUP BY uc.UserID,ct.ChannelTypeID
ORDER BY uc.UserID,ct.ChannelTypeID

Ok, so the insert works, and the records are distinct, and properly correspond to the asociated table datasets.  And I can walk through the data as I mentioned above.

HOW can I load the table, on a regular basis, going forward?  Nobody wants to do a manual insert/update.....
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36587027
Sorry, had too many meetings to respond sooner. As for establishing the relationship, I would argue that the application will need to handle that. I.e., the application would have a screen that assigns the established Channel Types to the User or vice versa. Depending on the design of the application there could be a possibility that when a new Channel Type is created that an option exists to add users also, and similarly when added a new User.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36587040
More importantly, HOW can I load the table, on a regular basis, going forward?  Nobody wants to do a manual insert/update.....

and no apologies necessary.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36587057
Do you have a list of users and what channel types then can use? Where does the relationship between these two entities come from? The data structures you have created are sound, but what business logic determines that (going back to your example) JohnDoe can use Channel Types 1,2,4 and 6?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36587086
No, this is determined on a case by case basis.  John Doe, for example, is created in UserComponents, with some basic supporting detail.  Varied teams speak, determine exactly how/where John Doe is going to focus, and then the channel types are assigned.  

Then, over time, maybe John's channel types are edited, or added to, or even deactivated (disabled).

Same thing with Jane, Mary, Bob.....


The actual types are determined, per user, based on an ever-changing business need.
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36588351
So the application that creates the User Components and Channel Types should have the functionality to administer Channel Types for User Components (or vice versa).

The question I have is: Is there some process external to your database (some other application) that connects UserComponents to ChannelTypes?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36588497
my answer:  not really... I have created a procedure to actually 'connect' the values, and return to the front end, upon request.

I am pretty ok with the deployment thus far, but a good question has just come to my attention:

•      How do you keep track of separate sets of channels and their associated users when you use the store procedures to add channel configuration records? For example, I can insert a set of configurations for channeltype A that goes to user X, and a completely different set that will go to user Y. There is no way for me to assign the channel configurations to a particular user.

This kind of gets me back to the problem of A., how do I load this Key table, and B., maybe I actually do need to incorporate it behind the two table insertions.  before this question, I was thinking i merely needed to load it behind the scenes, and i'd use it to return the data to the front end.  I think I've got to be smarter than that.

do you see the problem that I am referring to?
0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36588810
So your stored procedure contains logic that knows how to connect a UserComponent to a set of ChannelTypes. Wouldn't this stored procedure be able to load the data into the key table?

As to your question, since Channel Type Configuration appears to be a super set of Channel Types, you may want to consider having your Keys table go between your UserComponents and ChannelTypeConfiguration tables. It sounds like you are not really assigning a channel type specifically to the user but rather the configuration of the channel type.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36589533
>> It sounds like you are not really assigning a channel type specifically to the user but rather the configuration of the channel type.
Yes.  That is it.  

>>Wouldn't this stored procedure be able to load the data into the key table?
Yes, I suppose so.  If I were to post the curent proc, would you mind letting me know how you would approach it?

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36590060
I do not mind. Post away!
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36590434
I've been genericizing my posting all day long.... i have tried to be very consistent here.  Please let me know if anything isn't clear, shaun.

Thank you for looking.
IF OBJECT_ID('usp_GetUserChannels','p')>0
DROP PROC dbo.usp_GetUserChannels
GO
CREATE PROC dbo.usp_GetUserChannels (
 @UserName VARCHAR(25),
 @Instance INT
)
AS 
SET NOCOUNT ON;
/*
Allows for the return of active Channel Configuration values, for the given @UserName, @Intance.
This is used by the XXXX applications at startup.
EXEC dbo.usp_GetUserChannels @UserName = 'JohnDoe',@Instance = 1
EXEC dbo.usp_GetUserChannels @UserName = 'JohnDoe',@Instance = 2

Auth: Me
Date: 9/23/211
*/
BEGIN
	DECLARE @UserID INT
	IF EXISTS(SELECT 1 FROM dbo.UserComponents WHERE UserName = @UserName AND Instance = @Instance)
	BEGIN
		SET @UserID = (SELECT UserID FROM dbo.UserComponents WHERE UserName = @UserName AND Instance = @Instance)
	END
	ELSE
	BEGIN
		SELECT 'The given UserName/Instance does not exist. Please check your values.'
		RETURN;
	END

		SELECT 
--			uc.UserName,
--			uc.Instance,
			ct.ChannelConfigurationID,
			CASE ct.ChannelTypeID WHEN 4 THEN 'this type'
								  WHEN 5 THEN 'that type'
								  WHEN 6 THEN 'another type'
								  WHEN 7 THEN 'one more' END [ChannelType],
			ct.Configuration,
			REPLACE(ct.ConfigurationValue,'.00','') [Value]
		FROM
			dbo.UserComponents uc JOIN dbo.Keys k
			  ON uc.UserID = k.UserID JOIN dbo.ChannelTypes ct
				ON k.ChannelTypeID = ct.ChannelConfigurationID
		WHERE
			uc.UserName = @UserName
			AND uc.Instance = @Instance
			AND ct.ConfigurationValue <> 0

			IF @@ERROR <> 0
			BEGIN
				RAISERROR('Failed to return the requested Channel Configuration values.',16,1)
				RETURN;
			END

END

SET NOCOUNT OFF;

GO

Open in new window

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36600141
Your stored procedure will handle returning the data once it is in the database, but how will it be entered and maintained? In a previous comment you mentioned that varied teams determine which channels each user should have. How will this be recorded in your database? Is there some application that does it? (Hopefully not but)  Do these teams provide this information to you (the DBA team) and you enter it?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36709666
No, it is not an application.  There is an operational team, per se, that will insert them via my procedures, as needed.

So, if we can reverse the procedural return of the data, as you suggested, it seems like we ought to be able to use the same, to insert/post the data.  But, right now I am asking myself if I could not drastically simplify the whole thing, by adding UserID to the ChannelType table.  


>>>
I have a UserComponent table with UserID as PK
I have a ChannelType table with ChannelTypeID as PK
I have a third table, call it Keys, with two attributes; UserID,ChannelTypeID

ChannelTypeID is not in UserComponent table.
UserID is not in ChannelType table.  
>>>


0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36711065
Let's go back to the business case...
You have a set number of Channel Types: fullPacket, lastOrder, DeltaScore, Switch and BookQty (and possibly others). This sounds like a look up table.
A user is assigned one or many channel types and a configuration of that channel.
You user table is already defined (UserName and instance).
Your Channel Types table is already defined (ChannelTypeID, ChannelName and Active).

For your "Keys" table:
#1 If configurations are specific to the user, then your ChannelTypeConfiguration table sounds your true "Keys" table: UserName, ChannelTypeID, Configuration and Status.
#2 If configurations are "shared" between users, then you should add an ID/PK to your configurations table and have your Keys table join between the UserComponents and ChannelTypeConfiguration table.

To allow the operations team to manage the keys table, they would need to provide the data: UserName (and instance, if necessary), Channel Type (if by name, your stored procedure could look up the value in the ChannelTypes table) and configuration. Your stored procedure would then perform the update.

You can use the IF EXISTS (SELECT 1 FROM <TABLE> where <condition>) construct to determine if you need to perform an insert or update.

Ex.
IF EXISTS (SELECT 1 FROM Keys WHERE UserID = @UserID and ChannelTypeID = @ChannelTypeID)
  <Perform update process>
ELSE
  <Perform insert process>
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36716376
I totally needed to get back into this last night, but I fell asleep.  

Your business case assessment is pretty close, except for the ChannelTypes --
fullPacket, lastOrder, DeltaScore, Switch and BookQty are actually configurations for the channel types.  I know that my first post suggests otherwise, but I was still learning the data at that time.  the channel types would be Trade, Book,Price,Packet.  And the values such as DeltaScore, Switch, BookQty,FullPacket, and many more, are configurations specific to each channel type.

>>#1 If configurations are specific to the user, then your ChannelTypeConfiguration table sounds your true "Keys" table: UserName, ChannelTypeID, Configuration and Status.
Yes, that is it.  Each user has multiple channeltypes, and each channeltype has different configurations.  NOTE:  A user can actually have two of the same channeltypes, only with different configuration values.  ie, maybe he's got two Trade channel types, and the BookQty for the first one is 5, and for the second one, it is 10.

Right now, my Key table is only UserID (IDENTITY from UserComponents) and ConfigurationID (IDENTITY from my ChanneltypeConfiguration table).  

As you said earlier, the procedure to return the data works.  So it would seem we ought to be able to do the reverse... if exists, update.  else insert.  

I have posted the current procedure below, as I needed to change it to allow for the multiple channeltypes, per users, with different configurations.  (they're not really duplicates, but they sort of are.  the channeltype is the same, the channeltypeconfiguration is different.)  I handled this with ROW_NUMBER, so they'd be returned like this example:

ID      ChannelType      Configuration      Value
1      Book            SwitchQty                      0.10
1      Trade            BookScale      8
1      Trade            DeltaScore      0.10
1      Trade            FullPacket                5
1      Trade            FullPrice                      2
1      Trade            FullScale                      8
1      Trade            PacketTotal      240
1      Packet            PacketTotal      240
1      Trade            TradeScale      13
1      Packet            MinQty            1
1      Trade            MinQty            10
1      Trade            MinQty            1
1      Packet            MinQty            20
1      Trade            MinRatio                     0.10
1      Packet            MinRatio                     0.35
1      Packet            TradeScale      13
2      Trade            DeltaScore      0.30
2      Trade            FullPacket                         1
2      Trade            BookScale      1
2      Trade            TradeScale      23
2      Trade            MinQty            100
2      Packet            MinQty            5
2      Trade            MinRatio                      0.25

DeltaScore, for example, you can see is a configuration in there twice for the Trade channel type:
1      Trade            DeltaScore      0.10
2      Trade            DeltaScore      0.30


at this point, i am only asking, do I need to have the UserID in the ChannelTypeConfiguration table?  it may drastically simplify a lot of things... but, IF my Keys table is defined properly, then this would not be necessary, would it?

Do I have the components necessary to handle the many to many (a user can have many configurations, a configuration can have many users)?  

Let me see if I can get the EXISTS into my ins procedure.
CREATE PROC dbo.usp_GetUserChannels (
 @UserName VARCHAR(25),
 @Instance INT,
 @ChannelType VARCHAR(20) = NULL
)
AS 
SET NOCOUNT ON;
/*
Allows for the return of active Channel Configuration values, for the given @UserName, @IntanceID.
This is used by the XXX applications at startup.
EXEC dbo.usp_GetUserChannels @UserName = 'JohnDoe',@Instance = 1,@ChannelType = 'Trade'
EXEC dbo.usp_GetUserChannels @UserName = 'JohnDoe',@Instance = 0
EXEC dbo.usp_GetUserChannels @UserName = 'Janedoe',@Instance = 2

Auth: Me
Date: 9/28/211
*/
BEGIN
	DECLARE @UserID INT
	IF EXISTS(SELECT 1 FROM dbo.UserServerComponents WHERE UserName = @UserName AND Instance = @Instance)
	BEGIN
		SET @UserID = (SELECT UserID FROM dbo.UserServerComponents WHERE UserName = @UserName AND Instance = @Instance)
	END
	ELSE
	BEGIN
		SELECT 'The given UserName/Instance does not exist. Please check your values.'
		RETURN;
	END

	DECLARE @ChannelTypeID INT
	SELECT @ChannelTypeID = CASE WHEN @ChannelType = 'Trade' THEN 4
				  WHEN @ChannelType = 'Book' THEN 5
				  WHEN @ChannelType = 'Price' THEN 6
				  WHEN @ChannelType = 'Packet' THEN 7 END
		SELECT 
			ROW_NUMBER() OVER(PARTITION BY tc.ChannelTypeID,tc.Configuration ORDER BY tc.ChannelTypeID) [ID],
			CASE tc.ChannelTypeID WHEN 4 THEN 'Trade'
					  WHEN 5 THEN 'Book'
					  WHEN 6 THEN 'Price'
					  WHEN 7 THEN 'Packet' END [ChannelType],
			tc.Configuration,
			REPLACE(tc.ConfigurationValue,'.00','') [Value]
		FROM
			dbo.UserServerComponents ts JOIN dbo.Keys tsk
			  ON ts.UserID = tsk.UserID JOIN dbo.Configuration tc
				ON tsk.ConfigurationID = tc.ChannelConfigurationID
		WHERE
			ts.UserName = @UserName
			AND ts.Instance = @Instance
			AND tc.ConfigurationValue <> 0
			AND (@ChannelType IS NULL OR tc.ChannelTypeID = @ChannelTypeID)
		ORDER BY
			[ID],
			Configuration

			IF @@ERROR <> 0
			BEGIN
				RAISERROR('Failed to return the requested Channel Configuration values.',16,1)
				RETURN;
			END

END

SET NOCOUNT OFF;

GO

Open in new window

0
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36814422
If you add the UserID to the configurations table, you will lose the Many users to a single configuration.
As you keys table contains both configuration id and user id, you will have both scenarios: many users to a single configuration and a single user to multiple configurations.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 36816841
>>If you add the UserID to the configurations table, you will lose the Many users to a single configuration.
I understand what you're saying, and I don't want to do that.  I am just having an awful time coming up with the administration of the configuration data, driven by, or based on the key table.  

Using the key table, if value does exist in configuration table, then update it.  else, insert it.  upsert, basically.  
but, i have to allow for this:
>>>
NOTE:  A user can actually have two of the same channeltypes, only with different configuration values.  ie, maybe he's got two Trade channel types, and the BookQty for the first one is 5, and for the second one, it is 10.
>>>


how would you approach it, shaun?
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 500 total points
ID: 36818293
That one is tricky. There would be issues if your users are updating configurations that are shared between users. Questions that come to mind:
When inserting a new configuration, do you need to check if that configuration exists, or can you have duplicate configurations?
When updating a configuration, is it updated for all users, or do you create a new configuration for just that user (and then apply the answer to the question above).
Do you need to concern yourself with orphaned configurations when all users of a configuration have been removed?

I guess I'm advocating not using a many to many join table (your Keys table), and putting the user ID on the configuration table.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 36902888
Well, I was hoping for that key table... really seemed like a slick way to manage things.  But, I've added UserID into the configurations table, and I am moving forward.

Thank you for all of your input. Definitely appreciated.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Analysis of table use 7 30
MS SQL 2014 get SPIDs of users 6 27
sql server query? 6 28
Caste datetime 2 25
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now