[Building Sakai] Discussion: KNL-364 and KNL-500

[Jean-Francois Leveque]

Why shouldn't the session table also be in the history tables ? There 
are lots of other useful informations in SAKAI_SESSION.

I would be willing to reconsider my vote based on the answers to Ray's 
criteria to justify denormalization.

So far other criteria do not work for production, AFAICT.

J-F

Ian Boston a écrit :
> +1 to Stephens comment, iirc once the session table is cleaned, there  
> is no way of connecting an event to a user. Adding user id to each  
> record would make archiving for audit simpler.
> 
> Ian
> 
> Sent from my iPhone
> 
> On 22 May 2010, at 09:40, "Stephen Marquard"  
> <stephen.marquard at uct.ac.za> wrote:
> 
>> Also I'm guessing many sites denormalize the data anyway for  
>> archiving.
>>
>> We certainly do, i.e. our SAKAI_EVENT history tables (in a separate  
>> db)
>> have USER_ID added, otherwise queries on very large data sets (e.g.  
>> all
>> events in 2010 for a given user) become very slow.
>>
>> So I don't have an issue with the denormalization here - I think it
>> makes sense. As the data is written once and is immutable, there's no
>> risk of inconsistency, and it's a worthwhile tradeoff of some disk  
>> space
>> for performance.
>>
>> Regards
>> Stephen
>>
>>>>> Ray Davis <ray at media.berkeley.edu> 5/21/2010 6:01 PM >>>
>> In my experience, denormalization is a normal part of the relational  
>> DB
>>
>> development cycle on a relational DB. See, for example:
>>
>> http://www.agiledata.org/essays/dataModeling101.html#Denormalize
>> http://www.siue.edu/~dbock/cmis564/denormal.htm
>>
>> Basically, the workflow I'm used to is
>>
>> 1. Normalize as part of data analysis.
>> 2. Implement the "pure" code.
>> 3. Monitor performance/scalability to find bottlenecks.
>> 4. Denormalize where it looks like it would help.
>> 5. Go back to 3. :)
>>
>> What's needed to justify denormalization:
>> * Evidence that the denormalization actually improves things (since
>> intuition is often wrong)
>> * Modifications to the denormalized tables should be coordinated in a
>> single code location
>>
>> If both of those hold true in this case, would you be willing to
>> reconsider your vote?
>>
>> Best,
>> Ray
>>
>> On 5/21/10 7:58 AM, Jean-Francois Leveque wrote:
>>> -1 on adding USER_ID to SAKAI_EVENT, not a good idea from a DB point
>> of
>>> view : denormalization
>>>
>>> Why not caching?
>>>
>>> cheers,
>>> Jean-Francois
>>>
>>> Dunstall, Christopher a écrit :
>>>> Definitely a +1 for this.
>>>>
>>>> This would make statistics generation faster and less resource
>> hungry than it currently is.
>>>>
>>>> Chris Dunstall | Service Support - Applications
>>>> Technology Integration/OLE Virtual Team
>>>> Division of Information Technology | Charles Sturt University |
>> Bathurst, NSW
>>>> Ph: 02 63384818 | Fax: 02 63384181
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: sakai-dev-bounces at collab.sakaiproject.org
>> [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of
>> Steve Swinsburg
>>>> Sent: Wednesday, 19 May 2010 10:26 PM
>>>> To: sakai-dev at collab.sakaiproject.org Developers
>>>> Subject: [Building Sakai] Discussion: KNL-364 and KNL-500
>>>>
>>>> Some recent work in the UsageSessionService and EventTrackingService
>> API's has brought to the surface this old feature request, adding a
>> USER_ID field to the SAKAI_EVENT table.
>> http://jira.sakaiproject.org/browse/KNL-364
>>>> As it stands, to get the userId of the person who published an
>> event, you must go via the sessionId. This is achieved via:
>> UsageSessionService.getSession(id) however this is a database query,  
>> and
>> there is no cache so is a potential bottleneck.
>>>> Interestingly, Event.getUserId() has a comment in its Javadoc that
>> if the value is null, to use the session. However, this value is  
>> always
>> null, it's never set.
>>>> And while we are at it, might as well add a getter to the Event
>> object for the time field, since there is no way to get that either,
>> even though it is stored in the table.
>>>> http://jira.sakaiproject.org/browse/KNL-500
>>>>
>>>> Thoughts?
>>>>
>>>> cheers,
>>>> Steve