How do I represent this datastructure in Acccess 97? I want Effects to be a dynamic array of indexes to another table called Effects.
ID (primary key index)
Unit A (integer reference to a table called Units)
Unit B (integer reference to a table called Units)
Operator (integer reference to a table called Operators)
Effects (dynamic array of references to a tablecalled Effects)
What would be the best way to represent this structure?
I am reluctant to let the Effects field be a reference to an entire table (ex. there would be tables Effects1, Effects2, Effects3, ..., EffectsN)
Is there an alternative to this?
Perhaps it is possible to do something nifty with the use of OLE.
Another thing I have been considering is to let the Effects field contain a string of integers, each separated by a single space. But with this approach there would be no possibility of enforcing referential integrity between the Effects field and the Effects table.
I will raise the points to 200 if anyone has an answer!