W
Wladdy
Table A has 20 fields. It currently holds 4,000 records (12,000 in the
future).
Table B has 5 fileds. It currently holds 50 records (800 in the
future).
There is a one-to-many relationship from B to A:
Every record B has at least one "child" record in A.
Roughly 25% of records in A have one (and only one) corresponding
"parent" in B. The rest does not have any.
Furthermore, every record in B has at the very most 10 children in A
and the indexes of these children are always consecutive.
With this ratio, what is the best method to link A and B?
(1) Should I directly add a field to A linking to the index field B
(both integer fields), even though 75% records in A will have no value
for that field?
(2) Should I create an intermediate C table with just two fields,
linking each A index with its corresponding B index. Advantage: no
"wasted space" for elements of A with no parent in B. Inconvenient:
adds complexity to database.
(3) I could create in B two integer fields called x and y indicating
for each element of B the index of its first child record in A and
that of its last child record in A, respectively. I could then build
based on B a query Q with 10 fields [Child_n] nominatively listing
each corresponding child in A (ie: [Child_1]=x, [Child_2]=x+1, ...
[Child_10]=x+1 or IsNull, depending on x-y+1). FInally, I could build
a union query U with two fields, linking each index in A with each
corresponding index B. That query U would be much like the table
suggested in option (2). Advantage: no "wated space" for elements of A
with no parent in B; no extra table. Inconvenients: adds complexity to
databasa and results in non-updatable queries and forms.
Thanks you for reading, for understanding and for giving me your
advice.
W.
future).
Table B has 5 fileds. It currently holds 50 records (800 in the
future).
There is a one-to-many relationship from B to A:
Every record B has at least one "child" record in A.
Roughly 25% of records in A have one (and only one) corresponding
"parent" in B. The rest does not have any.
Furthermore, every record in B has at the very most 10 children in A
and the indexes of these children are always consecutive.
With this ratio, what is the best method to link A and B?
(1) Should I directly add a field to A linking to the index field B
(both integer fields), even though 75% records in A will have no value
for that field?
(2) Should I create an intermediate C table with just two fields,
linking each A index with its corresponding B index. Advantage: no
"wasted space" for elements of A with no parent in B. Inconvenient:
adds complexity to database.
(3) I could create in B two integer fields called x and y indicating
for each element of B the index of its first child record in A and
that of its last child record in A, respectively. I could then build
based on B a query Q with 10 fields [Child_n] nominatively listing
each corresponding child in A (ie: [Child_1]=x, [Child_2]=x+1, ...
[Child_10]=x+1 or IsNull, depending on x-y+1). FInally, I could build
a union query U with two fields, linking each index in A with each
corresponding index B. That query U would be much like the table
suggested in option (2). Advantage: no "wated space" for elements of A
with no parent in B; no extra table. Inconvenients: adds complexity to
databasa and results in non-updatable queries and forms.
Thanks you for reading, for understanding and for giving me your
advice.
W.