Hi Ruwan,
Lifecycle data are stored in a separate table (LC_DATA) which can be in a
different datastore. Here we want to duplicate the data in IDM_USER table
to increase the read performance. Join operation will not work since
Identity and Lifecycle data can be stored in two databases.
CREATE TABLE IF NOT EXISTS LC_DATA(
LC_STATE_ID VARCHAR(36),
LC_NAME VARCHAR(255),
LC_STATUS VARCHAR(255),
UNIQUE (LC_STATE_ID),
PRIMARY KEY (LC_STATE_ID)
);
Thanks
Isura.
On Mon, Feb 13, 2017 at 1:28 PM, Ruwan Abeykoon <[email protected]> wrote:
> HI Johann,
> I would think adding lifecycle state in "IDM_USER" is violation of
> database fundamentals.
>
>
> 1. IDM_USER will hold fairly static data and read most, updated less.
> 2. Lifecycle data will be modified mostly.
> 3. There is no noticeable performance gain/loss of Join vs having it in
> single table. [1], for properly written joins.
>
> I think it is best to keep all the lifecycle related data in independent
> table structure, since lifecycle management is separate feature.
>
> Have we encountered any specific performance issue in having then in
> different tables previously? if not, can we put all the lifecycle related
> info in its own tables, while keeping IDM_USER clean.
>
>
> [1] http://stackoverflow.com/questions/477226/sql-joins-vs-s
> ingle-table-performance-difference
>
>
> On Mon, Feb 13, 2017 at 1:03 PM, Johann Nallathamby <[email protected]>
> wrote:
>
>>
>>
>> On Mon, Feb 13, 2017 at 11:22 AM, Thanuja Jayasinghe <[email protected]>
>> wrote:
>>
>>> Hi Johann / Isura,
>>>
>>> On Tue, Feb 7, 2017 at 10:00 PM, Johann Nallathamby <[email protected]>
>>> wrote:
>>>
>>>>
>>>>
>>>> On Wed, Feb 8, 2017 at 9:25 AM, Isura Karunaratne <[email protected]>
>>>> wrote:
>>>>
>>>>> Hi Johann,
>>>>>
>>>>>
>>>>> On Wed, Feb 8, 2017 at 9:19 AM, Johann Nallathamby <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> For IS 6.0.0 M3 we decided to implement and manage user lifecycle
>>>>>> states. For IS 6.0.0 M2 we are implementing SCIM 2.0. I propose that we
>>>>>> extend SCIM 2.0 metadata and include the user lifecycle state as a user's
>>>>>> metadata.
>>>>>>
>>>>>> Also regarding where we need to store this metadata, I think it needs
>>>>>> to be stored in a column in the UM_USER table. This means we don't have
>>>>>> to
>>>>>> go to the identity store to fetch user's state for most of the operations
>>>>>> we will be performing on the user.
>>>>>>
>>>>>> Should we store state and lifecycleId in UM_USER table or IDM_USER
>>>>> table ?
>>>>>
>>>>
>>>> Sorry it should be IDM_USER. This will be irrespective of underlying
>>>> identity store type.
>>>>
>>>>
>>> IDM_USER table comes with the default UniqueUserIdResolver
>>> implementation. When we connect to an existing user store( basically when
>>> we have deferent UniqueUserIdResolver implementation), we can't garauntee
>>> that this table will be included in the schema.
>>>
>>> Since life cycle status is mandotery, it should not depend on the
>>> IDM_USER table to store values?
>>>
>>
>> Keeping lifecycle state in IDM_USER means now we treat lifecycle state as
>> a first class user metadata in our identity-mgt implementation. And also in
>> future we will treat all the SCIM 2.0 metadata also as first class in
>> identity-mgt implementation. This is purely for performance reasons. So we
>> should not be thinking as if lifecycle depends on identity-mgt. Even
>> without lifecycle-mgt that table must be populated now with initial
>> lifecycle state "CREATED". However we may not be able to transition between
>> states without lifecycle-mgt feature.
>>
>>>
>>>
>>>>
>>>>> Thanks
>>>>> Isura
>>>>>
>>>>>
>>>>>
>>>>>> On a different note I think we also need to merge the SCIM2.0
>>>>>> metadata table with UM_USER table so that all SCIM 2.0 metadata is
>>>>>> supported by our identity-mgt implementation. This will greatly improve
>>>>>> performance and avoid multiple lookups in database. Similarly we must use
>>>>>> UM_GROUP to store group metadata of SCIM 2.0.
>>>>>>
>>>>>> --
>>>>>> Thanks & Regards,
>>>>>>
>>>>>> *Johann Dilantha Nallathamby*
>>>>>> Technical Lead & Product Lead of WSO2 Identity Server
>>>>>> Governance Technologies Team
>>>>>> WSO2, Inc.
>>>>>> lean.enterprise.middleware
>>>>>>
>>>>>> Mobile - *+94777776950*
>>>>>> Blog - *http://nallaa.wordpress.com <http://nallaa.wordpress.com>*
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Thanks & Regards,
>>>>
>>>> *Johann Dilantha Nallathamby*
>>>> Technical Lead & Product Lead of WSO2 Identity Server
>>>> Governance Technologies Team
>>>> WSO2, Inc.
>>>> lean.enterprise.middleware
>>>>
>>>> Mobile - *+94777776950*
>>>> Blog - *http://nallaa.wordpress.com <http://nallaa.wordpress.com>*
>>>>
>>>> _______________________________________________
>>>> Architecture mailing list
>>>> [email protected]
>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
>>>>
>>>>
>>>
>>> Thanks,
>>> Thanuja
>>> --
>>> *Thanuja Lakmal*
>>> Senior Software Engineer
>>> WSO2 Inc. http://wso2.com/
>>> *lean.enterprise.middleware*
>>> Mobile: +94715979891 +94758009992
>>>
>>
>>
>>
>> --
>> Thanks & Regards,
>>
>> *Johann Dilantha Nallathamby*
>> Technical Lead & Product Lead of WSO2 Identity Server
>> Governance Technologies Team
>> WSO2, Inc.
>> lean.enterprise.middleware
>>
>> Mobile - *+94777776950*
>> Blog - *http://nallaa.wordpress.com <http://nallaa.wordpress.com>*
>>
>> _______________________________________________
>> Architecture mailing list
>> [email protected]
>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
>>
>>
>
>
> --
>
> *Ruwan Abeykoon*
> *Associate Director/Architect**,*
> *WSO2, Inc. http://wso2.com <https://wso2.com/signature> *
> *lean.enterprise.middleware.*
>
>
> _______________________________________________
> Architecture mailing list
> [email protected]
> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
>
>
_______________________________________________
Architecture mailing list
[email protected]
https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
