Overhead of a bunch of Reference Fields

Jaymer

OK, this example cannot use a normalized database structure.
So please no "can you change it this way" or "it will work better if..." replies.

Because there is a bug (or just the way it is implemented), a dropdown box with a custom query seems to be evaluated at the time a form is rendered... so if something that is entered on the form LOGICALLY changes the values of what should be in the lists, (thats why a dynamic choice list with a variable in the WHERE clause), the drop down OPTIONS are NOT changed during data entry. In fact, all drop down boxes will be empty because the WHERE clause will fail due to the conditional field not having a value yet. (OK, sound weird? stay with me.)
WITHOUT changing any code, if you save that record and come back in, then since there ARE values in the fields NOW, the dynamic choices will be populated and everything looks like its working fine.

In its simplest form... (this isn't real... its an example for discussing OVERHEAD of many Reference Fields)
Assume a Customer BO with GENRE to be selected as his/her favorite type of book.
I need to offer 8 suggested Books as soon as the GENRE is selected.
They must be in Drop Down boxes.
The 8 suggestions/drop downs for 8 DIFFERENT TIERS of books. Out of a 10,000 books, and 500 in SciFi, for example, those 500 are each in a specific TIER.
So, visually, select a GENRE and these 8 drop downs get populated.

OK, you might be saying, ask for GENRE first on another screen, then go to this screen... ok, thats not the point. In the REAL example it can't happen that way. Keep reading please.

OK, since the Dynamic Drop Down box won't populate correctly, I can make this work if I change all 8 fields to REFERENCE FIELDS pointing to the BookMaster BO.
It will all work fine.
But my main file is going to have millions of recs in it, each of those having 5-8 "book suggestions" in them.
If I make it a REFERENCE FIELD, then my Q is about all the extra overhead AwareIM is doing behind the scenes.
True, its only a simple PEER and not a more complicated 1-to-many. Most ppl are not worrying about adding a few Reference fields in a file, but here are 8 and its in my main HUGE transaction file.

SO, the Q for the experts, is:
If this is the way is has to be, do I worry about any long-term performance issues because of those 8 "redundant" fields?

Ideally, if the drop down value worked "differently", then I'd just have 8 regular plain text fields.

thanks
jaymer...

pureist

to succinctly clarify the original post:

When a Plain Text Attribute has Choices which are determined by a Query (Existing or Custom) the query will return zero records (and thus the dropdown will have no items) IF the query contains dynamic criteria obtained at runtime.

However, IF the query does not contain any criteria or if the criteria is static, then the query will return records.

However, IF a Reference Attribute is set to display as a dropdown and it has a Filter Query (Custom) which contains exactly the same Criteria as for the Plain Text Attribute Choices Query (Custom), then records ARE returned (including for a new instance).

EVEN IF the same Existing Query is set as the Reference Attribute Filter Query (Existing) as was used for the Plain Text Attribute with Choices determined by a Query (Existing), it will work fine (including for a new instance).

The issue of a Query used to filter results returning no records if its Criteria is dynamic - obtained at runtime, only occurs for Plain Text Attributes which has choices determined by a Query (Existing or Custom), but not for Reference Attributes set to display as a dropdown and with a Filter Query.