LookupTable with Referential Integrity and Cascaded Updates

[ApexData]

I have a 15 LookUp Tables tied to 15 ComboBoxes.
Some of the Tables contain 3 to 10 Recs, and a couple contain up to 50
Recs.

1- Is it a CommonPractice to link the field that gets the ComboBox
result, to the Lookup Table to ensure Referential Integrity and
Cascaded Updates?

2- Also, should the LookupTable PrimaryKey be an AutoKey or the Name
of the main field being referenced, which in my case would average 15
chars, but in two cases be 50-chars long? The AutoKey would be
smaller, but given the size of the Tables may not be necessary?

3- Should I store AutoKey value, or the Value of the main field being
referenced?

Thanks
Greg


eld or is the MainField sufficient as the Primary since each will be
unique, BUT may be up to 50 characters long in a few instances??

2- Should each LookUp Table be linked back to the maintable at the
ComboBox

[storrboy]

On Feb 26, 8:22 pm, "[e-mail]" <[e-mail]:

I have a 15 LookUp Tables tied to 15 ComboBoxes.
Some of the Tables contain 3 to 10 Recs, and a couple contain up to 50
Recs.

1- Is it a CommonPractice to link the field that gets the ComboBox
result, to the Lookup Table to ensure Referential Integrity and
Cascaded Updates?

Keep in mind that controls are primarily a means of entering and
viewing data. Don't think of them as being a part of the data or table
structure. If you are using (or intend to) Relationships and Integrety
then yes, the target table and the "lookup" should be connected on the
relevant field.

2- Also, should the LookupTable PrimaryKey be an AutoKey or the Name
of the main field being referenced, which in my case would average 15
chars, but in two cases be 50-chars long? The AutoKey would be
smaller, but given the size of the Tables may not be necessary?

The type of index you use is entirely up to you and the needs of
the application. Sometimes it is beneficial not to use AutoNumbers,
sometimes it is. I usually prefer them as the information they
represent can be changed without needing to affect other data.
Sometimes this is not a good thing. You decide what's best for your
app.

3- Should I store AutoKey value, or the Value of the main field being
referenced?

Whatever field the relationship is based on (usually an index
field) is what you would store.

[ApexData]

Thanks Mike

This will probably add an additional 15 Indexes that Access will have
to manage over a network just to ensure Cascading Changes to each
single field. It's nice to have the feature, but will this negatively
impact performance much?

I think I'd like to use the referenced field as the PrimaryKey instead
of an AutoKey, but am I asking to much from Access to deal with a
PrimaryKey field of 50-Chars long
over the network?

ThanksAgain
Greg

[storrboy]

On Feb 26, 7:57 pm, "[e-mail]" <[e-mail]:

Thanks Mike

This will probably add an additional 15 Indexes that Access will have
to manage over a network just to ensure Cascading Changes to each
single field. It's nice to have the feature, but will this negatively
impact performance much?

Not if as stated, the most you'd see in the table is 50 records.
Access will only transfer the indexes that are in use or requested,
not all of them (unless requested).
One other thing I've done in the past to reduce the number of "lookup"
tables is to combine a bunch into one. Mind you this is not really a
good method for selecting values that are a part of a large record,
but maybe for selecting Provinces, States, Currency Types, Format
options ect. As an example you might do this...

Table: tblCommon_Selections
SelID (PK) 'Index for selection
SelText 'Text or description of selection item
Catagory 'A way to group them for different combos

Example Data

ON / Ontario / Provs
PQ / Quebec / Provs
OH / Ohio / States
NY / New York / States
SD / Short Date / Formats
LD / Long Date / Formats

Different combos could filter for thier rowsource by including a
WHERE [Catagory] = blah blah

I think I'd like to use the referenced field as the PrimaryKey instead
of an AutoKey, but am I asking to much from Access to deal with a
PrimaryKey field of 50-Chars long
over the network?

Not really. Fifty chars is really not a lot of data - a 14.4K modem
would transfer that in less than a second. But I feel that an index
should be used as a short identifier, not actual data, so in my
opinion it kind of defeats the purpose. But there's not a rule that
says you can't do it that way.

[ApexData]

Once again, Thanks for all the valuable insight.
I will forge ahead with seperate Lookup Tables and ponder the index
issue.
I believe the performance will be fine. If not, I have other options
now.

Thanks
Greg