How important IS referential integrity?

[IncidentalProgrammer]

Hi, all!

I have a database in Access 2007 that I'm working on, and considering a second back-end for. I've already split what I have into FE and BE, but management wants a whole new series of tools added, and I'm considering putting that cluster into a secondary BE file, due to size and structure.

That said, I know that the big downside to having multiple BEs is that you can't enforce referencial integrity. However, everything I'm reading about the details of referencial integrity, it looks like I've covered manually: my users can't create new orphan records (for example, to create an Account, you first have to select the Client it belongs to), and can't create them by deleting records (I haven't given users a way to delete a record, they can only change its status to innactive, cancelled, etc.; due to the nature of the data and importance of keeping a complete history, all records must be kept).

So is that all I would have needed RI for? Or is there something that I'm missing?

My other question is if I have some forms in which related records from both BE files needs to be present and worked with, is that doable? Say I'm working on an insurance policy, where the bulk of the records related to it are in one BE, but the rates/premium is in the other BE file; given they have a common identifier, is there any reason I can't have both sets of data for the policy on one form, and work with/make changes to it?

[ranman256]

Its mostly needed for PARENT- CHILD table relations.
tClient -> tClientPayment

If you have integrity on this parent-child, and someone deletes the master client, it will prevent that (due to the RI) because it would orphan all the clients payments. The payments would have no master record to tell you who they belong to.
But you could also set it to delete all the children if the master client is deleted. (** dangerous) I prefer to NOT allow this.

It is NOT needed for 'lookup' tables. (i see this sometimes) Where someone will bind the tStates table to a tClient table on the State field.
Not needed. Its just for lookup, and does not need to be bound in RI.

[IncidentalProgrammer]

Thanks for the info!

It sounds like I really don't need it, in that case. To borrow your example, in my database, the users have no way to delete the master client, and orphan the children; if we stop doing business with "Hypothetical Client, Inc.", a user would go in, change the client's status to "Cancelled", and enter the date we ceased doing business with them and the reason ("lost to competitor", "program cancelled", etc.).

The only chance users really have to delete anything is as they're keying a new entry--they can delete an entry they've started and not finished, but not one that's already been completed.

[ranman256]

Id put it in as protection, with the NO deletes option.

[IncidentalProgrammer]

I will where I can, but what I was mainly worried about was whether not being able to enforce RI between multiple back-end files would be problematic.

[June7]

Certainly if users work directly with tables/queries and code is not in place behind forms to manage integrity.

I've never worked with db where data of multiple backends must have referential integrity between the files. Why would that be the case?

[ssanfu]

but management wants a whole new series of tools added, and I'm considering putting that cluster into a secondary BE file, due to size and structure.

This is confusing! ..... or I should say "I am confused by this statement".
The BE usually (should) only contain tables. What do you mean by "a whole new series of tools".
If you have a split dB, the FE can be up to 2GB and the BE can be up to 2GB.


And to your question "How important IS referential integrity?" I would answer "It is extremely important". If I can't trust my data to be correct/accurate, what good is the dB????

[IncidentalProgrammer]

June7: I wasn't sure if there was a reason that they would need RI enforced. This is the first/only database I've built, so I don't have any personal experience with operating multiple BEs, and was not sure if the lack of RI could cause issues. I do have things in place (or not in place, in the case of delete buttons!) to prevent orphan records outside of enforcing RI. Just wasn't sure if there was something that feature did that I wasn't aware of!

Ssanfu: I was condensing the scope of the project a little bit. Basically, on top of the relationship and policy management database I was already working on, I was given an additional project to add a huge new section that will handle underwriting tasks and some extremely large reports. Because the reports are based on huge import files, I worry that I could easily run afoul of the 2GB limit if I tried to mix it with the rest of the database, even though the imported data would only be stored long enough to crunch the numbers and generate reports (with resulting calculations stored in the database each month). Until I can move everything into SQL Server, I think this is probably going to be my safest bet. And I do have measures in place to manually enforce RI, just wanted to make sure that there wasn't something that it does that I wasn't aware of, and that might cause problems if it couldn't be enforced between the 2 BEs.

Thanks, guys!

[Perceptus]

If you can have those huge import files be stored as an Excel file, you could link the excel file into the database. keeping it free from sucking up the size.

[ssanfu]

....even though the imported data would only be stored long enough to crunch the numbers and generate reports (with resulting calculations stored in the database each month)....

So I didn't understand... (not the first time).

Yes, you can have the import data in a different BE and have both BEs linked to one FE. Since they are temp tables (effectively), you don't/shouldn't have to worry about RI. The linked tables would appear to be in a single BE. After doing the calculations the import BE data can be deleted.

This is similar to linking to Excel spreadsheets that Perceptus suggested.