Enumeration entity Primary Key

[masionas]

Hi Guys,

I have a number of the enumerations sets of different types but the problem is that they have same ids. Example:




But in the ofbiz enumeration entity enum_id is the only primary key, so I am not able to load those sets as is. Will it no be a problem with ofbiz dependant stuff if I change the primary key of Enumeration entity to be a composite key which will include two fields (enum_id, enum_type_id) ? Please, let me know if it can cause any problems to the legacy data model?

Thanks for any replies.

[Adrian Crum]

I have used unique IDs that have some kind of semantic meaning. For example:

enum_id       enum_desc
-----------   ---------
MATL_TYPE_1   Brick
MATL_TYPE_2   Paper
MATL_TYPE_3   Sand

METAL_TYPE_1  Gold
METAL_TYPE_2  Platinum
METAL_TYPE_3  Silver

-Adrian


MrJohnBrown wrote:

> Hi Guys,
>
> I have a number of the enumerations sets of different types but the problem
> is that they have same ids. Example:
>
>
> http://www.nabble.com/file/p11946308/enum.jpg 
>
> But in the ofbiz enumeration entity enum_id is the only primary key, so I am
> not able to load those sets as is. Will it no be a problem with ofbiz
> dependant stuff if I change the primary key of Enumeration entity to be a
> composite key which will include two fields (enum_id, enum_type_id) ?
> Please, let me know if it can cause any problems to the legacy data model?
>
> Thanks for any replies.

[masionas]

The problem is that these enum_ids in my case are used as the values in product table fields. And there is running "Order By" on them. So they are ordered from Low to High sets, in other words have grading. That is why in my old system I used the numeric 1,2,3..etc sequence. And then it was very easy to get sortable lists of products by those fields. In my old system I have each enumeration in its own table, so that was not a poblem with primary key. But in ofbiz it is the one table of all the enumerations that is why the problem has arised.

Adrian Crum wrote

I have used unique IDs that have some kind of semantic meaning. For example:

enum_id       enum_desc
-----------   ---------
MATL_TYPE_1   Brick
MATL_TYPE_2   Paper
MATL_TYPE_3   Sand

METAL_TYPE_1  Gold
METAL_TYPE_2  Platinum
METAL_TYPE_3  Silver

-Adrian


MrJohnBrown wrote:
> Hi Guys,
>
> I have a number of the enumerations sets of different types but the problem
> is that they have same ids. Example:
>
>
> http://www.nabble.com/file/p11946308/enum.jpg 
>
> But in the ofbiz enumeration entity enum_id is the only primary key, so I am
> not able to load those sets as is. Will it no be a problem with ofbiz
> dependant stuff if I change the primary key of Enumeration entity to be a
> composite key which will include two fields (enum_id, enum_type_id) ?
> Please, let me know if it can cause any problems to the legacy data model?
>
> Thanks for any replies.

[Adrian Crum]

The enumeration IDs in my example can't be sorted?

You could always go back to your original schema and have separate table for each type.


MrJohnBrown wrote:

> The problem is that these enum_ids in my case are used as the values in
> product table fields. And there is running "Order By" on them. So they are
> ordered from Low to High sets, in other words have grading. That is why in
> my old system I used the numeric 1,2,3..etc sequence. And then it was very
> easy to get sortable lists of products by those fields. In my old system I
> have each enumeration in its own table, so that was not a poblem with
> primary key. But in ofbiz it is the one table of all the enumerations that
> is why the problem has arised.
>
>
> Adrian Crum wrote:
>
>>I have used unique IDs that have some kind of semantic meaning. For
>>example:
>>
>>enum_id       enum_desc
>>-----------   ---------
>>MATL_TYPE_1   Brick
>>MATL_TYPE_2   Paper
>>MATL_TYPE_3   Sand
>>
>>METAL_TYPE_1  Gold
>>METAL_TYPE_2  Platinum
>>METAL_TYPE_3  Silver
>>
>>-Adrian
>>
>>
>>MrJohnBrown wrote:
>>
>>>Hi Guys,
>>>
>>>I have a number of the enumerations sets of different types but the
>>>problem
>>>is that they have same ids. Example:
>>>
>>>
>>>http://www.nabble.com/file/p11946308/enum.jpg 
>>>
>>>But in the ofbiz enumeration entity enum_id is the only primary key, so I
>>>am
>>>not able to load those sets as is. Will it no be a problem with ofbiz
>>>dependant stuff if I change the primary key of Enumeration entity to be a
>>>composite key which will include two fields (enum_id, enum_type_id) ?
>>>Please, let me know if it can cause any problems to the legacy data
>>>model?
>>>
>>>Thanks for any replies.
>>
>>
>

[masionas]

I guess I should try to use enumeration.sequnce_id as a replacement of my ids.

MrJohnBrown wrote

The problem is that these enum_ids in my case are used as the values in product table fields. And there is running "Order By" on them. So they are ordered from Low to High sets, in other words have grading. That is why in my old system I used the numeric 1,2,3..etc sequence. And then it was very easy to get sortable lists of products by those fields. In my old system I have each enumeration in its own table, so that was not a poblem with primary key. But in ofbiz it is the one table of all the enumerations that is why the problem has arised.

Adrian Crum wrote

I have used unique IDs that have some kind of semantic meaning. For example:

enum_id       enum_desc
-----------   ---------
MATL_TYPE_1   Brick
MATL_TYPE_2   Paper
MATL_TYPE_3   Sand

METAL_TYPE_1  Gold
METAL_TYPE_2  Platinum
METAL_TYPE_3  Silver

-Adrian


MrJohnBrown wrote:
> Hi Guys,
>
> I have a number of the enumerations sets of different types but the problem
> is that they have same ids. Example:
>
>
> http://www.nabble.com/file/p11946308/enum.jpg 
>
> But in the ofbiz enumeration entity enum_id is the only primary key, so I am
> not able to load those sets as is. Will it no be a problem with ofbiz
> dependant stuff if I change the primary key of Enumeration entity to be a
> composite key which will include two fields (enum_id, enum_type_id) ?
> Please, let me know if it can cause any problems to the legacy data model?
>
> Thanks for any replies.

[masionas]

If you have more than 9 entries it won't sort correctly. please, see the picture:
Query is

select * from enumeration where enum_type_id = 'TEST' order by enum_id asc

result is

Adrian Crum wrote

The enumeration IDs in my example can't be sorted?

You could always go back to your original schema and have separate table for each type.


MrJohnBrown wrote:

> The problem is that these enum_ids in my case are used as the values in
> product table fields. And there is running "Order By" on them. So they are
> ordered from Low to High sets, in other words have grading. That is why in
> my old system I used the numeric 1,2,3..etc sequence. And then it was very
> easy to get sortable lists of products by those fields. In my old system I
> have each enumeration in its own table, so that was not a poblem with
> primary key. But in ofbiz it is the one table of all the enumerations that
> is why the problem has arised.
>
>
> Adrian Crum wrote:
>
>>I have used unique IDs that have some kind of semantic meaning. For
>>example:
>>
>>enum_id       enum_desc
>>-----------   ---------
>>MATL_TYPE_1   Brick
>>MATL_TYPE_2   Paper
>>MATL_TYPE_3   Sand
>>
>>METAL_TYPE_1  Gold
>>METAL_TYPE_2  Platinum
>>METAL_TYPE_3  Silver
>>
>>-Adrian
>>
>>
>>MrJohnBrown wrote:
>>
>>>Hi Guys,
>>>
>>>I have a number of the enumerations sets of different types but the
>>>problem
>>>is that they have same ids. Example:
>>>
>>>
>>>http://www.nabble.com/file/p11946308/enum.jpg 
>>>
>>>But in the ofbiz enumeration entity enum_id is the only primary key, so I
>>>am
>>>not able to load those sets as is. Will it no be a problem with ofbiz
>>>dependant stuff if I change the primary key of Enumeration entity to be a
>>>composite key which will include two fields (enum_id, enum_type_id) ?
>>>Please, let me know if it can cause any problems to the legacy data
>>>model?
>>>
>>>Thanks for any replies.
>>
>>
>

[Adrian Crum]

Use 2 digit numbers in ALL of the IDs, so they can be compared correctly.

MrJohnBrown wrote:

> If you have more than 9 entries it won't sort correctly. please, see the
> picture:
> Query is
>
> select * from enumeration where enum_type_id = 'TEST' order by enum_id asc
>
> result is
>
> http://www.nabble.com/file/p11947980/enum2.jpg 
>
>
>
> Adrian Crum wrote:
>
>>The enumeration IDs in my example can't be sorted?
>>
>>You could always go back to your original schema and have separate table
>>for each type.
>>
>>
>>MrJohnBrown wrote:
>>
>>
>>>The problem is that these enum_ids in my case are used as the values in
>>>product table fields. And there is running "Order By" on them. So they
>>>are
>>>ordered from Low to High sets, in other words have grading. That is why
>>>in
>>>my old system I used the numeric 1,2,3..etc sequence. And then it was
>>>very
>>>easy to get sortable lists of products by those fields. In my old system
>>>I
>>>have each enumeration in its own table, so that was not a poblem with
>>>primary key. But in ofbiz it is the one table of all the enumerations
>>>that
>>>is why the problem has arised.
>>>
>>>
>>>Adrian Crum wrote:
>>>
>>>
>>>>I have used unique IDs that have some kind of semantic meaning. For
>>>>example:
>>>>
>>>>enum_id       enum_desc
>>>>-----------   ---------
>>>>MATL_TYPE_1   Brick
>>>>MATL_TYPE_2   Paper
>>>>MATL_TYPE_3   Sand
>>>>
>>>>METAL_TYPE_1  Gold
>>>>METAL_TYPE_2  Platinum
>>>>METAL_TYPE_3  Silver
>>>>
>>>>-Adrian
>>>>
>>>>
>>>>MrJohnBrown wrote:
>>>>
>>>>
>>>>>Hi Guys,
>>>>>
>>>>>I have a number of the enumerations sets of different types but the
>>>>>problem
>>>>>is that they have same ids. Example:
>>>>>
>>>>>
>>>>>http://www.nabble.com/file/p11946308/enum.jpg 
>>>>>
>>>>>But in the ofbiz enumeration entity enum_id is the only primary key, so
>
> I
>
>>>>>am
>>>>>not able to load those sets as is. Will it no be a problem with ofbiz
>>>>>dependant stuff if I change the primary key of Enumeration entity to be
>
> a
>
>>>>>composite key which will include two fields (enum_id, enum_type_id) ?
>>>>>Please, let me know if it can cause any problems to the legacy data
>>>>>model?
>>>>>
>>>>>Thanks for any replies.
>>>>
>>>>
>>
>

[masionas]

Yes, it should work out. Thanks, Adrian.

Adrian Crum wrote

Use 2 digit numbers in ALL of the IDs, so they can be compared correctly.

MrJohnBrown wrote:

> If you have more than 9 entries it won't sort correctly. please, see the
> picture:
> Query is
>
> select * from enumeration where enum_type_id = 'TEST' order by enum_id asc
>
> result is
>
> http://www.nabble.com/file/p11947980/enum2.jpg 
>
>
>
> Adrian Crum wrote:
>
>>The enumeration IDs in my example can't be sorted?
>>
>>You could always go back to your original schema and have separate table
>>for each type.
>>
>>
>>MrJohnBrown wrote:
>>
>>
>>>The problem is that these enum_ids in my case are used as the values in
>>>product table fields. And there is running "Order By" on them. So they
>>>are
>>>ordered from Low to High sets, in other words have grading. That is why
>>>in
>>>my old system I used the numeric 1,2,3..etc sequence. And then it was
>>>very
>>>easy to get sortable lists of products by those fields. In my old system
>>>I
>>>have each enumeration in its own table, so that was not a poblem with
>>>primary key. But in ofbiz it is the one table of all the enumerations
>>>that
>>>is why the problem has arised.
>>>
>>>
>>>Adrian Crum wrote:
>>>
>>>
>>>>I have used unique IDs that have some kind of semantic meaning. For
>>>>example:
>>>>
>>>>enum_id       enum_desc
>>>>-----------   ---------
>>>>MATL_TYPE_1   Brick
>>>>MATL_TYPE_2   Paper
>>>>MATL_TYPE_3   Sand
>>>>
>>>>METAL_TYPE_1  Gold
>>>>METAL_TYPE_2  Platinum
>>>>METAL_TYPE_3  Silver
>>>>
>>>>-Adrian
>>>>
>>>>
>>>>MrJohnBrown wrote:
>>>>
>>>>
>>>>>Hi Guys,
>>>>>
>>>>>I have a number of the enumerations sets of different types but the
>>>>>problem
>>>>>is that they have same ids. Example:
>>>>>
>>>>>
>>>>>http://www.nabble.com/file/p11946308/enum.jpg 
>>>>>
>>>>>But in the ofbiz enumeration entity enum_id is the only primary key, so
>
> I
>
>>>>>am
>>>>>not able to load those sets as is. Will it no be a problem with ofbiz
>>>>>dependant stuff if I change the primary key of Enumeration entity to be
>
> a
>
>>>>>composite key which will include two fields (enum_id, enum_type_id) ?
>>>>>Please, let me know if it can cause any problems to the legacy data
>>>>>model?
>>>>>
>>>>>Thanks for any replies.
>>>>
>>>>
>>
>