1)  Your description of your lookup tables (small number of fixed text entries) are one of the few exceptions where I don't always use an autonumber primary key.  It almost seems like overkill in some cases.  Although it is probably still a "best practice" to use that numeric PK anyhow.  Consider a lookup table of statuses.   If there is a potential for the name of a status to change (for example we once had a client who wanted to split "closed" inro "Closed-Fixed", "Closed-Withdrawn", etc with "Closed-Fixed" meaning the same thing as the original "Closed"), a numeric PK identifier can save you some trouble.

2) Leave your Lookup tables in the BackEnd.  Generally, the only tables that should be in the front-end are things like temp tables, or tables that are relevant only to the user of a given copy of a front-end.  A good example is a Local Version table, which keeps track of the release version of front ends deployed to different users.

If I have table validation rules that depend on the lookups (Field X must be completed of lookup field = Y or Z, for example) and I move the lookup tables to the front end, where would I put those validation rules?

I recommend the backend, whether they're likely to change or not. If they're in the backend, you can (and should) use them to enforce referential integrity on the master tables.

If they're in the frontend, and even one record change is required, you have to redistribute the app.


http://www.myaccessprogram.com/Tips_Best_Practices.html

 Not sure I understand; your validation rule is only that a field is required.  If your talking about enforcements of relationships, then yes the table would need to remain in the backend.

  You can only enforce relationships within a DB container.

Jim.

sparab, I don't believe I can implement referential integrity between backend and frontend tables.

Jim, frex I have a table tblTasks with fields fldStatus (which is validated against a lookup table) and fldResultSummary.  If fldStatus = "Planned" or "In progress" than fldResultSummary must be Null. If flsStatus = "Complete" or "Recorded" then fldResultSummary must not be Null. With the lookup tables in the backend, this validation is coded as a table validation rule. if I move the lookup tables to the front end, I can still do that, but if the valid values for the lookup tables change, so might the validation rules, so would it be better to enforce them in VBA for frmTask (which is the data entry form for tblTask)?

  I've never been a fan of table level validation rules because they were too limited in the past.  As a result, I do everything in forms (or in other languages, in a business object), which is why you got the answer you got.

  Typically however I do keep my lookup table in the BE as it's a single lookup table that supports multiple types as I outlined.

  But there are "lookup" tables such as my menu system that I keep in the front end as the menus don't change unless the app changes.   I have others as well such as tblSystemEventTypes (app login/logout, form opened, task run, etc), which don't change unless the app changes.

  With all that said, there is really no right or wrong answer here.  It's what works for you and the situation you have.  I would say though that if you can get away with it (it's static) and still do everything you need to do, then it's better off in the FE for performance reasons.

Jim.

<<True. However, I agree with Miriam for the case where say the 'status' field might be only one character, in which case it's more efficient to store 1byte instead of 4.  And you can enable Cascade Updates to cover any case where a status character is changed to a different value the the original.>>

  I never checked into this, but I once had someone argue that a long was still faster on a 32 bit machine as it's a full register compare where as the single character of 8 bits would require register shifts before compares could be made.

 I've never dug quite that deep, so I don't know if that holds water or not.  But you certainly could pack more keys on an index page with a single character, which I think would far out weigh any performance gain you might get on a long through hardware.

Jim.

I guess we would have to test that ...
mx

There's a fair bit to think about here. It's getting late here (UK) so I'll look at the pros and cons first thing tomorrow.

ok ...

Ok.

It seems pretty obvious that I should definitely change the lookup tables to have an autonumber PK, for performance, since I don't have single character statuses.

The FE-BE decision is less clear-cut. I'd like to maintain the RI and the table-level validation in the backend - the only data validation code I have at present in the frontend is the stuff that can't be defined in the tables. If the lookup values change (which would only be to add statuses, in all probability) it won't be too onerous to code an upgrade routine for the backends (of which there will be many) -- there's already a version number field in a backend config table. So I'm leaning towards leaving the tables in the backend.

Whether to mirror the lookup tables between BE and FE? If it gives me a significant performance or reliability advantage, it might be worth doing... The application will be run primarily in a single-PC environment (both FE and BE on the same PC) but some users may choose to run it in a networked environment.

"Whether to mirror the lookup tables between BE and FE? If it gives me a significant performance or reliability advantage, it might be worth doing.."
That will very likely be the case ... think about it ... Each time a user clicks on a drop down list ... not delay.

mx

I've finally gone for: Autonumber PK fields with tables mirrored in the FE and BE to get the performance benefits and the RI/data validation.