Solved Backend Data

[MattBaldry]

Hi all,

Would there be any pros/cons to splitting my backend data into data types? At the moment I have a single backend containing all my tables. I was wondering if there would be any benefit in breaking this down into categories, IE Quote Data, Customer Data, Supplier Data, Employee Data and so on.

I have over 50 tables in the current Data file. The Quote Data accounts for 9 tables. Customer Data has 8.

~Matt

 

[MajP]

I do not think you will have a way to enforce referential integrity. That could be extremely bad IMO. If it is too big move it to SQL Server or something else.

 

[jdraw]

Matt,
Your questions requires some context. You don't just have 50 tables. Your tables represent something. eg Customer, Product, Task.... Someone created or identified 50 tables. The tables have names. The tables contain fields/columns. When you have multiple tables there is often some relationship between/among tables. eg Customers may have 1 or more Orders. An Order may have 0,1 or more detail lines. An Order Detail line may represent a Product or Service...

All of this to say your 50 tables and their relationships will typically represent some processes within your business.
Can you tell us what business this database is intended to support? Can you provide some description of the business facts to help us understand the relationships?

 

[GPGeorge]

I do not think you will have a way to enforce referential integrity. That could be extremely bad IMO. If it is too big move it to SQL Server or something else.

Correct. Referential Integrity can only be enforced on tables within a single database, whether that is an ACE database, SQL Server, MySQL, etc.

If the tables in two different functional areas have no relationships with each other, that could be the basis for a division, I suppose. However, it's the kind of design that creates ripples of unease down my spine. More over, if you can have Quote data with no relationship to Customer data, it's likely that the basic relational design is suspect in the first place. Quotes are developed for Customers and prospective customers; it's hard to envision them being independent of each other.

If your single accdb -- which I hope is a Back End only -- is approaching or exceeding 1GB, it might be time to consider upgrading to the free version of SQL Server, SQL Server Express.

 

[isladogs]

Apart from maintaining referential integrity, another reason to not split your backend data into two or more databases is that maintaining a persistent connection to the BE significantly improves performance.
If you have several BEs then that might mean the need for a persistent connection to each

 

[MattBaldry]

Thanks guys,

I shall leave it as it is. It is not a major issue, was just curious as I have seen other databases done this way.

~Matt

 

[Solo712]

Hi all,

Would there be any pros/cons to splitting my backend data into data types? At the moment I have a single backend containing all my tables. I was wondering if there would be any benefit in breaking this down into categories, IE Quote Data, Customer Data, Supplier Data, Employee Data and so on.

I have over 50 tables in the current Data file. The Quote Data accounts for 9 tables. Customer Data has 8.

~Matt

Hi Matt,
though there may be issues with referential integrity in splitting the BE (if related tables end up in different files), there may also be benefits in performance and controlling the size of the back end(s). I use a cascaded switchboard which basically addresses the BEs independently by functions. So my condo management system has a Main management FE module with all the basic layout of the condo (buildings, units, owners, condo manager data board of directors, banks, legal stuff, insurance etc.). Owners/Receivables FE module manages condo fees, owner correspondence, fines, liens, assigned storage, parking and occupancy, which are kept in a different BE with links to relevant tables in the Main. Finally, the Common Elements FE module manages suppliers, payables, property maintenance and renovation plan, is also a different BE with links to the other two. It was originally three completely different databases, but this format I found useful in controlling the size of the back ends, and, obviously, in removing data duplicates in adding to the design. I had no issues with the system on account of addressing multiple BEs.

Best,
Jiri