Question To Create or Not to Create multiple backends (1 Viewer)

[genesis]

while a single access database file has a size limitation of 2 gigabyte, I have been given two options in my previous post and those are:

a. upsize to sql server or
b. you can always create multiple backends.

And so since I am not yet familiar with nor have backgrounds nor have learned about sql server yet, I opted to take option b.

however, in my recent post here in the forum, I was advised not to create multiple backends because it will cause me trouble in the future.

this is my situation:

Backend Databases:

1. customer database
2. supplier database
3. user database
4. company database
5. products(items) databases
6. transactions databases
7. tax database (holds tax types and related items)
8. etc

Frontend Databse:

All the backends databases (TABLES) are linked into this front end.

Purpose:

The purpose of multiple backends are:

1. for synchronization purposes to the main office/company backend databases
2. for anticipation that some databases will reach its 2 gigabyte limit in the years to come.


So I dont understand now why they given me option to create multiple backends and now advice me not to do so. I cant seem to understand why. Any feedbacks ?

 

[HiTechCoach]

I use multiple back ends a lot. I have for years without any issues. I even have back end in different formats. Some table in an Access database and some table in an SQL server.

With multiple back ends, you can not enforce RI between table in different back ends. You have to write your own code to enforce it.

I am really curious:
What was the reason not to have multiple back ends?

 

[genesis]

They said that creating multiple back ends would give me difficulty in the future. I cant remember whats the difficulty they said to me.

Maybe its about the relationship of tables.

What do you mean about

With multiple back ends, you can not enforce RI between table in different back ends. You have to write your own code to enforce it.

I have no idea what is RI? what is it HiTechCoach and whats the importance of it?

 

[John Big Booty]

....

I have no idea what is RI? what is it HiTechCoach and whats the importance of it?

RI = Referential integrity

 

[John Big Booty]

Ensuring RI is a corner stone of good DB design along with normalisation

Failure to understand either of these concepts will ultimately lead you into a world of pain.

 

[gemma-the-husky]

just to clarify

genesis posted some jpegs in the "how to talk to a programmer thread"

what i think he has, is a SEPARATE backend database for EACH table, because he is concerned about the 2GB limit

 

[John Big Booty]

.....

what i think he has, is a SEPARATE backend database for EACH table, because he is concerned about the 2GB limit

Is that approach not going to complicate the task of maintaining referential integrity?

 

[gemma-the-husky]

thats what i said, among other things - but he seems determined to continue in this way ... i may have it wrong, but thats what it seemed to me.

i thought the "front end" he was using was more designed to provide a single interface to a group of databases, but i got the impression he was using it in a different way, as above

 

[genesis]

thanks for giving out the meaning John Big Booty.

what i think he has, is a SEPARATE backend database for EACH table, because he is concerned about the 2GB limit

that is true Dave. that is my intention. I havent found problem on my RI as of the moment. And another site I would like to mention is that I was one program that I have downloaded from the internet using access as FE and BE, it uses multiple BE such as what I did.

As HiTechCoach mentioned:

I use multiple back ends a lot. I have for years without any issues. I even have back end in different formats. Some table in an Access database and some table in an SQL server.

With multiple back ends, you can not enforce RI between table in different back ends. You have to write your own code to enforce it.

I am really curious:
What was the reason not to have multiple back ends?

though I havent got idea about writing code to enforce RI so maybe I have to research it again.

Maybe HiTechCoach can give us input above that.

 

[HiTechCoach]

Whenever possible, you probable should keep all of your tables in a single back end. Only when you start to reach the file size limit, would you then consider the option of splitting into multiple back ends or better yet, upsizing to an SQL server.

Before the more power RDMS of today, there was no built in RI. We had to write our own code to enforce RI within the front end application.


To enforce RI, if you try to delete a parent record that has related record in a child table, you should not be able to delete the parent record and leave any orphaned records. You could write code for your delete function to check for related records in all the related tables. If any are found, you would not allow the record to be deleted. This is what a RDBMS does for you if you set it to enforce RI without having to write the code yourself.

With JET (Access's database engine), the RI can be automatically enforced by the database engine without having to write any code in the Front End to handle it. Since it is enforced by the back end's database engine, there is no way that any front end can circumvent it. That is why I prefer to have the databse engine enforce the RI whenever possible.

 

[genesis]

Oh I see. Yes I have also noticed that but I didnt know that was the purpose of RI. Yes ofcourse coding that is alot easier knowing what is the purpose of that RI.

HiTechCoach, yes it is better if I just place all my tables in one DB. But I opted for using multiple DB from the beginning and part of my design.

Actually I have never made RI on my previous database projects and they still work with no problem. Though as a relational database to be called as such, they should be have RI properly established.

 

[HiTechCoach]

Actually I have never made RI on my previous database projects and they still work with no problem.

Have you ever looked for orphaned records to verify that it really is working properly?

In your database will all the tables in a single database, try turning on RI. If you have integrity issues, then you will not be able to turn in RI for the relationship. If it allows you to set the RI, then your data is good.

 

[genesis]

only when I create query that needs relationship I create relationship between tables.

I do not establish Referential Integrity.

 

[Rabbie]

I do not establish Referential Integrity.

Why not? It makes it easy to ensure you don't get a mass of orphaned records which can adversely affect performance. And as you have already said a relational DB should enforce RI.

 

[Atomic Shrimp]

If the tables are growing to the extent that they all need separate databases, isn't that an indication that they might grow further so that some of them may individually become too large?

If it's important enough to be planning by splitting them all out, I think it's important enough to deserve migration to a more powerful DB solution.

 

[HiTechCoach]

only when I create query that needs relationship I create relationship between tables.

I do not establish Referential Integrity.

I figure that was the case.

I was responding to the fact that you think your databases have been working just fine. I was recommend that you take a copy of your database and create all the RI relationships. If you can enable RI, then your database was working properly. If not, then your database has integrity issues.

 

[genesis]

If the tables are growing to the extent that they all need separate databases, isn't that an indication that they might grow further so that some of them may individually become too large?

If it's important enough to be planning by splitting them all out, I think it's important enough to deserve migration to a more powerful DB solution.

actually not. it is just an anticipation for the years to come while I am still learning mysql. whew, it is hard for me to grasp the idea of sql now when I am stuck with a database file type. I need more time for it to learn.

 

[genesis]

Why not? It makes it easy to ensure you don't get a mass of orphaned records which can adversely affect performance. And as you have already said a relational DB should enforce RI.

yes Rabbie, it would be definitely easy to establish RI to become fully relational DB, however when I learned in my project that establishing RI is for the purpose of cascading updates/deletes so as not to get problems with orphaned records, I have not need of it because I have manually created a code to check for orphans for me.

 

[genesis]

I figure that was the case.

I was responding to the fact that you think your databases have been working just fine. I was recommend that you take a copy of your database and create all the RI relationships. If you can enable RI, then your database was working properly. If not, then your database has integrity issues.

yes I have tested it without the RI and I created query and codes and it work just fine for now for me.

May I ask you Sir, you said that you also...

I use multiple back ends a lot. I have for years without any issues. I even have back end in different formats. Some table in an Access database and some table in an SQL server.

May I ask if you have problems with RI there or how did you establish your RI? I mean did you enable your RI? Was you database working fine with or without RI? Did you have integrity issues?

 

[HiTechCoach]

May I ask if you have problems with RI there or how did you establish your RI? I mean did you enable your RI? Was you database working fine with or without RI? Did you have integrity issues?

I always enforce RI. If I can let the database engine do it, that is the easiest and IMHO the best way. So whenever possible , you keep all tables in a single database that are getting data entry from the users.

As the need arises, then I do split the tables out between multiple back ends. For example, I will but he logging table in a SQL server and not in an Access back end. RI can not be enforced on this table since it logs records that have been delete from other tables.

This means that you must plan how you split your tables between back ends. I would do it in groups of tables so that you can let the back end enforce the RI. I would never plan it so that I have a separate back end for each table. IMHO, this would never be a good solution. You would need to not use JET/ACE for the back end if you must do this.

Remember, for JET/ACE (Access) or an SQL server to be the most effective as an RDBMS, you want ALL tables in a single database. That is always my goal!

I now start will all table in a access back end. When the need arises, I upsize the data to an SQL server, relink the front end and keep going. There has not been a lot to learn or change to switch back ends from JET/ACE (Access) to an SQL server.

See:
Beginner's Guide to ODBC

Beginning SQL Server Development