-
Jul 15th, 2016, 10:23 AM
#1
Thread Starter
PowerPoster
[RESOLVED] designing a table maintaining recent customer access
I googled around for this to get some ideas but I must not be using a good search string. My users want to see a history maintained of the last 20 or so customers they've accessed in our customer service application. So when they're running the app, they have a list they can click an entry and easily bring that customer up. I thought I would create a table with customer number, the id of the application user, and the date they accessed the customer. But let's say there's 20 rows in the table for user MMOCK. If I access my 21st customer, he becomes #1 and since I've maxed out the 20 do I push the last one out of the list by deleting the record? I don't really like deleting records behind the scenes like that, but of course I don't want the list to grow to thousands of customers when I'm only interested in 20.
Thanks for any ideas, or links to code that does this since I am having trouble finding any.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 10:43 AM
#2
Re: designing a table maintaining recent customer access
I suppose you can create a view and have it do a Select top 20 based on the Connection time for a user.
as example:
Code:
CREATE VIEW test20 AS
SELECT TOP 20 * FROM dbo.FoodDescription
ORDER BY FoodDescriptionId
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jul 15th, 2016, 10:56 AM
#3
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
So I would order by last-accessed...
But I don't want to just get the top 20. At any given time, there should be 20 or less records for an id and customer numbers. In the beginning, the table grows from 1 record to 2...to 20 and stops there.
When I want to insert my 21st record, I make room for it by deleting the oldest. So I want to select all the records, not top anything.
Note, this data doesn't exist yet. I'm sure we have datelastmodified of customer records, but I want to know if a record has even just been looked at so I think we'll need a new table and it would be:
CustAccessedNumber
CustAccessedBy
CustAccessedWhen
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 12:06 PM
#4
Re: designing a table maintaining recent customer access
You could add a where clause to the select from the view to get specific criteria
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Jul 15th, 2016, 12:20 PM
#5
Re: designing a table maintaining recent customer access
This table is new - and it's got a very unique USER INTERFACE requirement. It's not a data requirement.
Although I never use triggers - I could see a trigger being used on INSERT that would DELETE the 21'st record if it exists.
Or you could ARCHIVE the 21'st record so that your dislike of DELETE goes away (I also dislike DELETE).
Or you could simply never delete from this table - why are you thinking you need to delete the rows?
-
Jul 15th, 2016, 12:27 PM
#6
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Hi Steve, nice to hear from you!
Yes! I was thinking trigger too. I will look into that.
I think I "need" to delete rows because it is like old clothes in my basement that I am never going to wear again - why leave them hanging around?
Alternatively, I guess if the row existed and was like #80 in my orderby accessed-date dataset, I could update the accessed-date and then I don't have to do an insert.
Right?
And yes, new table, have complete control over it, brainstorming...thanks to both of you but my main concern was the delete, how to do it and if necessary. If we have 100 employees accessing 1000 customers...I know that's not a big table but it could be a much smaller table by keeping only the 20 custs per user.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 12:28 PM
#7
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Until my users decide they want to see their last 30 customers, LOL!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 12:53 PM
#8
Re: designing a table maintaining recent customer access
How about a primary key of USER ID + CUSTOMER ID?
That way if you visit cust #5 and then #6 and then #7 and then #5 again you only have "3" rows in the table. That #5 entry needs to have it's last access date changed - as opposed to adding a new row.
If a user only visits the same 100 customers over and over again then they only have 100 rows in the table.
You could always have logic that clears out "old" rows if the count reaches something high - let's say 200 rows for a USER.
-
Jul 15th, 2016, 12:55 PM
#9
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
I agree. That is what I was thinking of as the PK. And same for rest of it. Thanks; just needed my thoughts in/validated!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 01:57 PM
#10
Re: designing a table maintaining recent customer access
Just throwing this out there:
UserMRU (table)
-----------------
ID - some pkey
UserID - Identifies the user
SEQ - integer - sequence number, sequence is for each user, so numbers will repeat
Page/ViewId/ItemId - what ever it is you're using to identify what the user viewed
OK, then in the stored proc:
create procedure usp_addNewMRU(@UserID ______, ItemID ______)
update UserMRU
set SEQ = SEQ +1
from UserMRU where USerID = @UserID
delete from UserMRU where USerID = @UserID and SEQ > 20
insert into UserMRU (ID, UserId, SEQ, ItemID) values ({pkey generator}, @UserId, 1, @ItemID)
it shift everythign down in the list, removes anything beyond 20, and inserts the new item at the top... it doesn't even need to be 20... you could actually have a user config that indicates how many itmes to keep in the MRU... someone may only want 5 items, someone else 25....
Then to pull the MRU - Select ItemID from USerMRU where UserID = @USerID order by SEQ
that will get you everything in the user's MRU list, with the most recent at the top...
-tg
-
Jul 15th, 2016, 02:16 PM
#11
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
tg - yes, I was also kicking around an idea like shifting them down from 1 to 2, 2 to 3, etc. Thanks for the template to do that.
I am having a problem right now that has me climbing the walls... I've clicked in a customer in my list so I want to update that record's CustAccessedWhen timestamp. I've defined an UPDATE command; it goes like this:
Code:
UPDATE xtblCustomerAccessHistory
SET CustAccessedWhen = GETDATE()
WHERE (CustAccessedNumber = @CustNo) AND (CustAccessedBy = @EmpId)
How the heck do I call it???
It is my UpdateCommand of my TableAdapter.
So I am instantiating the table adapter, but when I say .Update(), it wants one of four things: a datatable, dataset, array of datarows or a datarow (I think that's right). Where does this command exist that I can call it?
I think I am just tired and bleary-eyed. But a co-worker is celebrating his 70th birthday, so maybe a piece of cake will help... (I was wondering if tg's idea would help me here, of course I'd be scratching this rather than addressing the issue which I don't like to do unless it's unequivocally better, but I would have to figure there too how to update an existing row into the top spot).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 15th, 2016, 03:10 PM
#12
Re: designing a table maintaining recent customer access
If it's just an update then you shouldn't be using the TableAdaptor... but rather a SQLCommand ... add the parameters to it, then call .ExecuteNonQuery ... I'd wrap it up into the MRU update, so it's one call. The MRU gets updated, the accessed timestamp is updated, and the system is up to date.
-tg
-
Jul 15th, 2016, 03:33 PM
#13
Re: designing a table maintaining recent customer access
@tg - your example does not handle the "same customer" being reviewed again. You could have a user toggle between customer A and B ten times and then have only those two customers in the pool.
There is a benefit speed wise to simply having a PK of USERID+CUSTOMERID. Only that single record needs to be updated.
In your SPROC you are updating up to 20 rows - deleting a row (potentially) and always INSERT'ing a new row. That's 22 hits on a table.
There is a lot of table contention and table locking that might go on - and it's all superfluous to actual data operations.
@mmock - have you considered just holding this info locally in a file on the hard drive of the user's PC?
By using a table you are making it confusing if the same "user id" logs in on two different PC's.
Just some food for thought...
-
Jul 16th, 2016, 06:46 AM
#14
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Originally Posted by szlamany
...have you considered just holding this info locally in a file on the hard drive of the user's PC?
By using a table you are making it confusing if the same "user id" logs in on two different PC's.
No, I had not considered that at all. Isn't it more confusing if the user has one list on one PC and a different list on another PC? Especially if he uses one PC not very often, he would go to that one and see a very old list?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 06:49 AM
#15
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Originally Posted by techgnome
If it's just an update then you shouldn't be using the TableAdaptor... but rather a SQLCommand ... add the parameters to it, then call .ExecuteNonQuery ... -tg
But I generated an SQL Update commannd in my table adapter. I thought that was what jmcilhinney was explaininghere.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 07:16 AM
#16
Re: designing a table maintaining recent customer access
I mentioned the same user at multiple locations more or less as a question to you about how it would/should behave. I don't know if your users move around like that to two PC's. If you were to consider the local storage of this info then that question should be addressed.
As to your last post about the SQL Update...
The SQL Update is about some grid of data you have - right? This last access stuff is related to what customer the user selects on the screen - right? That is not an update moment - they might just be looking at a list of customers from what I was understanding you wanted to do.
-
Jul 16th, 2016, 07:21 AM
#17
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
If I understand what you are asking, specifically I have a devexpress listboxcontrol. When you open up our customer service form, it displays your 20 mru customers. (Well not yet, but I will get there!) So you can double-click on one of these customers, or find one not there yet (via other means), and that customer then either moves up or is inserted for the first time into the top slot.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 07:31 AM
#18
Re: designing a table maintaining recent customer access
Originally Posted by MMock
If I understand what you are asking, specifically I have a devexpress listboxcontrol. When you open up our customer service form, it displays your 20 mru customers. (Well not yet, but I will get there!) So you can double-click on one of these customers, or find one not there yet (via other means),
I broke your quote in two - doesn't something happen right here?
Like a SELECT is run somewhere that brings that customer data into your customer service form? I would think that is the logical moment where you would take the Customer Id and make that top of the MRU list.
and that customer then either moves up or is inserted for the first time into the top slot.
-
Jul 16th, 2016, 07:43 AM
#19
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Are you talking about *when* it happens? I am trying to work on *how* it happens. This particular piece (mru customers) is new to an existing application (that is not quite accurate - the app exists in VB6 and I am porting it to .NET and as I port, here and there my boss asks for new functionality). So I 1) created this new table 2) manually put two rows in it 3) displayed those two rows in the listbox 4) want to click 2nd row and make it the first.
MS has an example; this is what I want to do:
Code:
private void UpdateButton_Click(object sender, EventArgs e)
{
Int32 newRegionID = 5;
try
{
regionTableAdapter1.Update(newRegionID, "Updated Region Description", 5, "NorthEastern");
}
catch (Exception ex)
{
MessageBox.Show("Update Failed");
}
RefreshDataset();
}
(Code from https://msdn.microsoft.com/en-us/lib...code-snippet-3)
Call an Update command and pass it some parameters. I created that command. But intellisense isn't showing it to me when I type tableadapter-dot-update-openparen. WHy is the Update command above taking 3 params? My Update command only wants a DataRow, array of datarows, dataset, datatable.
This has to be something basic and simple I am overlooking.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 08:04 AM
#20
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Thanks for helping me on a Saturday. I think I know what it might be...but I have to head off and teach an aerobics class! Not really where I want to be right now, but I'l test my theory when I get back.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 10:06 AM
#21
Re: designing a table maintaining recent customer access
I'm out for a while myself...
-
Jul 16th, 2016, 10:17 AM
#22
Re: designing a table maintaining recent customer access
lets see if i understand correctly
1)a user can never have more then 20 records in the 'accessed' table
2)the combination of userID and customerID can only exist once
if so:
if the combination of userID and customerID exist then
change the date
else
if the count of the userID is < 20 then
insert a new record
else
change the customerID and the date of the oldest record (with that userID)
end if
end if
do not put off till tomorrow what you can put off forever
-
Jul 16th, 2016, 11:42 AM
#23
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Originally Posted by IkkeEnGij
change the customerID and the date of the oldest record (with that userID)
Not if it (the customer id) is part of the PK, which I intend on it being.
Last edited by MMock; Jul 16th, 2016 at 11:49 AM.
Reason: clarified
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 11:48 AM
#24
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Originally Posted by MMock
I know what it might be...
I finally got a helpful exception and I saw I was missing EmpID as part of the dr columns.
Then I got an exception that the Update command didn't exist ("update requires a valid updatecommand"). It must've gotten deleted when I added the Emp ID? Because I definitely verified it existed earlier.
And finally, it worked.
So I was right, it was just something simple I was missing, but it took me while to see what that was...
So now that that's all set I can take a step back again and figure out how to only have 20 rows, or if I only want 20 rows, etc.
Thanks; I will take everyone's input into consideration!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 02:15 PM
#25
Re: designing a table maintaining recent customer access
Originally Posted by MMock
Not if it (the customer id) is part of the PK, which I intend on it being.
well,for what i propose there HAS to be a key in the accessed table that is a combination of userid and customerid
so, if you plan to make it the primary key, thats perfectly fine
do not put off till tomorrow what you can put off forever
-
Jul 16th, 2016, 03:35 PM
#26
Re: designing a table maintaining recent customer access
If I was doing this - and maybe since I am in a rich-web-app instead...
I would first have a SPROC that got the customer data for the customer service form. I'm guessing you have some SELECT command currently.
Since I have a SPROC I could easily modify that SPROC to not only do the current SELECT but also all this new logic for the MRU tables.
I would then modify that SPROC to also return the additional data needed to "refresh" the MRU list.
That way the refresh is guaranteed to match the reality of that table and more important the reality of the SELECT of the customer data.
It's just cart-before-the-horse, imo - updating the MRU list and then going to get the customer.
-
Jul 16th, 2016, 03:58 PM
#27
Re: designing a table maintaining recent customer access
Originally Posted by IkkeEnGij
well,for what i propose there HAS to be a key in the accessed table that is a combination of userid and customerid
so, if you plan to make it the primary key, thats perfectly fine
Bear in mind that changing any part of a primary key (or any key for that matter) is going to cause additional overhead.
SQL is designed to do "fast" SELECT's. The next fastest thing is an UPDATE to a non-indexed field - as that's only one I/O.
I think their is an important need for a PK of USERID+CUSTOMERID - it respects the single customer id per user requirement.
With that said the SPROC logic to handle the MRU would end up being this simple.
Code:
Update UserMRU Set LastAccessed=GetDate()
Where UserId=@UserId and CustId=@CustId
If @@RowCount=0 -- note that the following logic ONLY runs when
Begin -- the above UPDATE did not find the CustId
Insert into UserMRU values (@UserId, @CustId, GetDate())
Declare @CustList Table (RowNum int, CustId int, LastAccessed datetime)
Insert into @CustList
Select Row_Number() Over (Order by LastAccessed Desc)
,CustId, LastAccessed
From CustMRU
Where UserId=@UserId
If @@RowCount>50 -- note that I am using 50 so as to lessen the amount
Begin -- of time that DELETE's are used against the table
Delete From CustMRU
Where UserId=@UserId
and LastAccessed<(Select LastAccessed From @CustList Where RowNum=21)
End
Select Top 20 CustId From @CustList Order by RowNum
End
Else
Begin
Select Top 20 CustId From CustMRU Order by LastAccessed Desc
End
The above logic will do only the FAST UPDATE (which is unavoidable) and in the case of an existing CUSTID for that USERID - it will do the even faster SELECT.
If the UPDATE did no record - it will INSERT and then fast SELECT into a table variable (in memory). This table variable will serve the need of deleting (a SLOW SQL action) and then the required fast SELECT to finish things up. I decided to do the DELETE when the row count is over 50 for a USERID.
I'm debating with myself whether a clustered PK would be best. I think it would. SQL stores like 8000+ bytes in a page - that's a lot of USERID+CUSTID combinations. It will partially fill that page - so that INSERT's are less painful - that's just the nature of SQL. With an 8000 byte page it's really possible that a single I/O will bring forth the page that we need for a USERID. Then when you reach 50 records for a USERID 60% of the space is regained for that USERID.
Last edited by szlamany; Jul 16th, 2016 at 04:02 PM.
-
Jul 16th, 2016, 04:02 PM
#28
Re: designing a table maintaining recent customer access
@mmock - are you against SPROCS or are you not able to use them in your app here?
-
Jul 16th, 2016, 07:24 PM
#29
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
No...not against sprocs and yes can use them. I guess I just got into table adapter mode.
I mentioned this is a port of an existing VB6 system. Whatever he was already doing in in-line sql, I am using table adapters. When he used a sproc, so am I.
Now i have my own decision to make since this table and functionality are new - sproc or ta? But I really don't want to re-write the calls that populate the customer service form. There are a lot of tables and 2 db's accessed. So I'm not adverse to writing just this piece in a sproc, but don't want to rewrite all of it.
Sometimes I think port = quick conversion, and I don't want management getting mad at me for re-creating too much that I didn't need to. It's not new development which of course takes a long time.
And you've basically written my sproc, so now I would feel guilty not using it.
But I spent a lot of time figuring out problems, and would hate throwing all that away...especially as it was weekend time!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 16th, 2016, 09:01 PM
#30
Re: designing a table maintaining recent customer access
No worries!
That was free-thinking SPROC writing!
Can't a TA call a SPROC in the SQL statement just as easily?
-
Jul 17th, 2016, 04:56 AM
#31
Re: designing a table maintaining recent customer access
Originally Posted by szlamany
Bear in mind that changing any part of a primary key (or any key for that matter) is going to cause additional overhead.
yeah,what can i say about that ?
except maybe,that it satifies the OP's (implied) wishes of:
1) never having more then 20 records
2) never delete records
3) never update more then 1 record per customer accessed
do not put off till tomorrow what you can put off forever
-
Jul 17th, 2016, 10:00 AM
#32
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Originally Posted by szlamany
Can't a TA call a SPROC in the SQL statement just as easily?
Yes, I said that wrong. Even in TA mode I can still use a sproc. Will get input from original VB6 programmer, my "boss".
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 18th, 2016, 09:49 AM
#33
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
I wrote my sproc! It is a little bit like yours but not exactly. I thought I should understand what I was writing and not just steal yours.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 18th, 2016, 12:40 PM
#34
Thread Starter
PowerPoster
Re: designing a table maintaining recent customer access
Sproc written, code is calling it, I've tested and published it so VB6 guy and company owner can test. Thanks for all the help!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Jul 18th, 2016, 02:08 PM
#35
Re: [RESOLVED] designing a table maintaining recent customer access
Glad to hear you worked this out - good stuff!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|