Linking tables from a DB into a back end DB to use through fronten

[elsamiro2351]

I have a DB design question. I will describe the actual design, and
hopefully someone will give me a clue on how to design it correctly.

Frontend:
With all the usual content, expcept tables. Will be used by multiple users
at the same time.

Backend:
All the tables, related to each other with referential integrity (updates,
deletes)

ContainerDB:
Containing all the data. Per customer/year 3 tables. (Axxxx,Bxxxx,Cxxxx)

---

problem1:
If I link the containerDB's tables i want to work on into the backend, i
loose the referential integrity. RI doesn't work on linked tables.

problem2:
Else when I copy the tables data (Axxxx,Bxxxx,Cxxxx) to "work tables"
(Aworktable,Bworktable,..) RI should work, multiple users cannot use the
frontend at the same time they would overwrite each others work. Also data
is not updated realtime in the containerDB, which is desirable.

 

[BruceM]

Each user should have their own copy of the front end.

I don't understand why there is a backend and a container DB. That being
said, AFAIK referential integrity in the BE database will be enforced even
though it can't be enforced in linked tables. That is, RI in the BE is
inherited by the FE linked tables. However, if you are linking to more than
one BE file, you cannot enforce RI between the separate databases. In that
case you have to design the FE in such a way that RI is maintained. It
could be by data validation in a form's Before Update event, or other such
means.