Clarification on indexing

ask180

Hi,

Assume I have two business objects, A and B, in a one-to-many relationship (ie: one instance of A can relate to multiple instances of B). The relationship was set up by creating an attribute on B of type A. All basic stuff.

If A has 1 million rows, and B has 5 million rows, I want the attribute on B that points to A to be indexed, so that I can quickly retrieve all B rows that relate to a selected A instance. Again, normal administrative system behaviour.

The problem is that I can't see where in the configuration tool to set the attribute on B that points to A (a foreign key basically) to be indexed. For other types of attributes, there is a check box called 'Indexed', but I can't find it for this one.

Do I have to go into the database and manually create an index via SQL?

Any advice would be appreciated.

BobK

ask180 wrote
The problem is that I can't see where in the configuration tool to set the attribute on B that points to A (a foreign key basically) to be indexed. For other types of attributes, there is a check box called 'Indexed', but I can't find it for this one.

AwareIM automatically creates and maintains the necessary attributes for all relationship. Since there should not be any reason for the user to touch these attributes, they do not show up in the config tool

ask180 wrote
Do I have to go into the database and manually create an index via SQL?

No, you do not need to create any index for relationships. AwareIM creates and maintains the indexes for all relationships.

Having said the above, there is one issue you should be aware of.

Depending how you create the relationship, AwareIM may either
1) create a reference table called A_ref that is used to connect table A with table B (using your example tables A and B)
2) add 3 new attributes to table B. These attributes end in _REN, _RID and _RMA and point back to table A

I believe the consensus of users is that 2 above is more efficient. There is a posting somewhere on this forum that goes into more details about how to control which method AwareIM uses.

PointsWell

BobK wrote
I believe the consensus of users is that 2 above is more efficient. There is a posting somewhere on this forum that goes into more details about how to control which method AwareIM uses.

Always add the reference from the singular side. Obviously does not work on m-n relationships or simple multiple lists

For example: Companies have Employees, but an Employee only works at one Company, create Employee.psEmployer will result in the _REN, _RID and _RMA to be on the Employee table. If you alternatively create Company.pmEmployees first as a multiple attribute then you will end up with the Company_ref table.

Jaymer

HERE IS the discussion BobK mentioned about Ref tables and relationships.

ask180

Guys,

Thanks for the clarification. I will proceed with confidence that performance won't be an issue.