Split once but is more better?

[Sullaway]

I have a DB and it is split. What I would like to know is it a good idea to have the backend split more than once. In other words if I have Customers in one backend with all the tables that will hold everything about a customer we will need to know but in another backend have a table that will handle Orders and keep all the info on Orders, then another that may keep all the tables on Inventory.

I have tried to research and see if there are any pro's or con's to doing this but so far have not found anything. I have found plenty talking about the advantages of splitting a DB but none saying anything about splitting it more than once in the backend.

Is this a bad idea? Would there be headaches to doing this?

Thanks ahead of time for educating me.
Sullaway

 

[RickSpr]

The main disadvantage I know about is that you can't enforce relational integrity across database boundaries. If you put Customers in one back end and Orders in another, Access can't ensure that every order will always have a valid customer key, and if you delete a customer his orders can't be cascade-deleted, or if you change your customer key the orders will be orphaned.

There would also be a minor inefficiency in opening and maintaining multiple connections to the different databases, and multiple lock files for them. This would consume memory and time on your front end workstations, but as I said it's probably quite minor.

I can imagine some special circumstances where having multiple back ends would bring an advantage. For example, I'm working on a mainframe database conversion project right now, and have a data dictionary in Access. I need to join that with information about the system being converted, but I don't want to add the conversion tables into the data dictionary, where it would only exist temporarily and isn't useful to all its users, so I'm planning to have a separate back end for the conversion information. (Yes, that means I'm going to have to take responsibility for enforcing referential integrity in my code for the conversion front end.)

Another situation might be one where some of the tables contain very sensitive data. To help ensure only authorized people see them, you could put them in a separate database that has a database password.

In the typical case, though, I think having multiple back ends is probably more of a disadvantage than an advantage. The potential loss of referential integrity is a heavy price to pay for whatever benefits you may derive, in my opinion. Rick Sprague

 

[MichaelRed]

While I would generaly agree with Rick's commentary, I think it aplies to individuals who actually use the capabilities of the relations available in Ms. Access. From the question itself, it is aparent that you are not using the relationships. Therefore some of his points simply do not apply. In many situations, there CAN be reasons to segregate the tables in a databse to more than one actual (physical) database. Rick has mentioned a few, I could add a few, and I am sure that the 'community' could pile on many additional examples.

Personally, I would offer two general thoughts. First, if you are going to use a relational database, the use the inherent capabilities to your advantage. This, in general, includes the RELATIONSHIPS, such that cascade effecs are implemented. This can be daunting - at first. You will end up with unexpected errors when you attempt some operations in opposition to the defined relationships. They will be perplexing and frustrating. You will get over it, to find that many of the features save a considerable amount of time/effort in maintaining the database.

Second. Most databses are simply 'state models' of real world processes or functions. The various tables are simply stored states of different instances of the model. So, look at the 'real world' function you are modeling in the database. If - in that world - the model SHOULD be seperated, then you database PROBABLY should be seperated. If, on the otherhand, your real worl function is integrated, then your database PROBABLY should remain integrated. Ask the question. Does this part exist without the other part? Can ther be any orders without the customers? Will there be any orders without the customers? Are there VALID relationships between orders and sales (or customers) which need to be enforced?

Be careful. Some of these are NOT what they appear to be. Some of them change between situations. For instance, a wholesaler would usually NOT want to commit to the sale of merchandise which is not it stock, thus inventory and sales NEED to be tied together in a relationship. A retailer, on the other hand, would look VERY foolish if the Point of sale system refused to accept the sale of an item because the inventory system should none in stock.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Sullaway]

Rick and Micheal,

Thank you for your responses. You have told me exactly what I needed to hear in order to make my decisions.

I appreciate your time,
Shane