FE/BE Should Any Tables be Distributed in FE?

[AngelSpeaks]

Hi Everyone! So I have my database split and I have a couple of tables that actually drive the FE that would be maintained by me and probably changed with each FE release.

Purpose of the application. It's a mini accounting system. In my travels as a self employed accountant, I found that my clients just couldn't figure out QuickBooks and were not proficient enough to use Excel. Since I'm retired and don't want to purchase a new license (I can't stand QuickBooks Online and Desktop is now going subscription. I just found out that my version, is limited to three installations, so replacing my old desktop and having to have my new laptop's harddrive erased, I can't reload the software and QB wants me to buy a new version. I have it on another laptop, so I am transitioning everything off of it. I've been using QB for my personal accounts, my daughter's accounts (I am her representative payee for Social Security, plus as guardians, we are sometimes required to provide a report to the Courts on what we do with her money) and my husband and I have side businesses. My little accounting system is perfect for these needs, and there is a need for parents who are representative payees for their children). The database started small, but I got bored and kept adding to it because I like to learn new things.

Some examples are:

The following are used to fill list/comboboxes for users to select. They drive the creation of the representative payee and reporting needed for preparation of tax returns (Schedule C, Partnership/Corporations, Special Needs Trusts, Guardianship Reporting).
tblReports - reports titles and object names with requirements, such as prompt for dates, names, accounts. New reports could be added.
tblTaxLine - controls which controls which accounts appears on the representative payee and tax reports
tblAccountTypes - controls the sequence and grouping of the accounts for reporting.

Could these tables remain in the FE? Oh and because the Infernal Revenue Service and state taxation revenue agents all have every software imaginable on their computers, when they audit you, they want your computer files. So the BE's are separated (like they are in QuickBooks and all my tax preparation software). I have a form to select the 'company' and it gets relinked. Another reason why I would like these tables in the FE.

 

[tvanstiphout]

Tables can be in the FE. For example, I keep tblSystemSettings_FE in the FE. It includes a field indicating where the BE is.
Your tblReports can certainly be in the FE as well.
I'm not so sure about lookup tables (tblTaxLine and tblAccountTypes appear to be such tables): since their ID values are used as foreign key values in related tables, and since you'd want to enforce referential integrity, those tables should be in the BE. To use an example from the Northwind Dev edition sample database: both Companies and CompanyTypes belong in the same BE, because there is a relation between them.

 

[MarkK]

You should store data where it makes the most sense in service of its purpose. For instance, if the reports are in the FE, and you have data concerned with the correct functioning and configuration of reports, then that data belongs in the FE. AccountTypes, by contrast could be either. If there is a tblAccount in the BE, and it has a field called AccountTypeID and it is linked to tblAccountType on that ID, then tblAccountType should be in the BE. If tblAccountType is more meta-data, with details of what different account types mean in respect to your UI, then it should be if the FE. Make the decision based on purpose.

 

[GPGeorge]

There is a class of tables that can, and probably should, be delivered in the FE.

These are the configuration, or application, tables which store information about the application itself, not data related to the business.

In NW Developer Edition, you'll find a table of this kind called SystemSettings.

 

[AngelSpeaks]

Tables can be in the FE. For example, I keep tblSystemSettings_FE in the FE. It includes a field indicating where the BE is.
Your tblReports can certainly be in the FE as well.
I'm not so sure about lookup tables (tblTaxLine and tblAccountTypes appear to be such tables): since their ID values are used as foreign key values in related tables, and since you'd want to enforce referential integrity, those tables should be in the BE. To use an example from the Northwind Dev edition sample database: both Companies and CompanyTypes belong in the same BE, because there is a relation between them.

Thanks

 

[AngelSpeaks]

You should store data where it makes the most sense in service of its purpose. For instance, if the reports are in the FE, and you have data concerned with the correct functioning and configuration of reports, then that data belongs in the FE. AccountTypes, by contrast could be either. If there is a tblAccount in the BE, and it has a field called AccountTypeID and it is linked to tblAccountType on that ID, then tblAccountType should be in the BE. If tblAccountType is more meta-data, with details of what different account types mean in respect to your UI, then it should be if the FE. Make the decision based on purpose.

Thanks

 

[AngelSpeaks]

There is a class of tables that can, and probably should, be delivered in the FE.

These are the configuration, or application, tables which store information about the application itself, not data related to the business.

In NW Developer Edition, you'll find a table of this kind called SystemSettings.
View attachment 118629

Thanks. I do have a settings table

 

[The_Doc_Man]

I stored some lookup tables that had to do with defining the names for some "state" variables (Open, Pending, Closed, Rejected, ... - that kind of state) and some other translation tables in the FE. If they only changed when the FE version was changed, that was stable enough to let it stay in the FE. If it changed more often, probably it belonged in the BE or in a side-end table.

 

[AngelSpeaks]

I stored some lookup tables that had to do with defining the names for some "state" variables (Open, Pending, Closed, Rejected, ... - that kind of state) and some other translation tables in the FE. If they only changed when the FE version was changed, that was stable enough to let it stay in the FE. If it changed more often, probably it belonged in the BE or in a side-end table.

Thanks

 

[isladogs]

Also store user defined system tables such as USysRibbons in the FE as that is where they take effect.

 

[AngelSpeaks]

Also store user defined system tables such as USysRibbons in the FE as that is where they take effect.

Thanks

 

[Cotswold]

I have always store tables in the FE.
There are system tables that you do not want your users to change. Or they are tables that you need to control the system. You may have standard groups which if changed would affect operations. Maybe a standard VAT file? You wouldn't want changes to that, particularly for dates prior to the current VAT rate and date range.
I always have a system table that holds system settings, license details, number of users etc, etc. Although some of that is in a protected state, it is important to the system and not a BE table that you cannot easily access. Tables in the FE give you control. You can more easily add and amend data in the FE for new versions issued.

 

[AngelSpeaks]

I have always store tables in the FE.
There are system tables that you do not want your users to change. Or they are tables that you need to control the system. You may have standard groups which if changed would affect operations. Maybe a standard VAT file? You wouldn't want changes to that, particularly for dates prior to the current VAT rate and date range.
I always have a system table that holds system settings, license details, number of users etc, etc. Although some of that is in a protected state, it is important to the system and not a BE table that you cannot easily access. Tables in the FE give you control. You can more easily add and amend data in the FE for new versions issued.

That was my thought.

 

[Pat Hartman]

My line of demarcation is simple. If the user can update the data in the table, it belongs in the BE. Otherwise, I normally keep it in the FE. So, as the others have mentioned, menus, system settings that I control and any tables where changes would not function if I didn't change the code.

System settings that the user controls are kept in the BE associated with the userID. That way, the user doesn't have to reenter his settings when he gets a new version of the FE.

 

[Mike Krailo]

System settings that the user controls are kept in the BE associated with the userID. That way, the user doesn't have to reenter his settings when he gets a new version of the FE.

I was wondering about that, makes sense.