I am trying to develop an office management system that will provide not only the ability to track and/or process internal procedures BUT also provide support for external clients. This is a CPA firm that needs to track a number of internal procedures such as tax return status and W2 filing status etc. Each of these are seperate tables. In addition, there are services provided to various clients that also require separate tables. Some of the services for the clients includes mailings etc. I am setting up a menu that will access to any of these functions. What is the best way to handle the data? I know I need to add the table etc., for each function/client to the master database but do I keep them in their individual folders or do I take them out of their folders. I am getting warnings that some of the things that I am trying to do, could cause problems when I try to set this up on the client's system. This whole process requires very strict naming conventions so that I do not mix my apples and oranges. Any suggestions would be appreciated.
-
-
Re: Many tables in database
Barry,
I can see where you are going to some degree. May I make a few suggestions? You may have better ones come along, but years have helped me.
1 you can have as many companies as you want sharing a table by doing your home work now. A field with a designation code for each company can help. I have designed my software so one app can contain up to 99(could be more) car dealers at a time. each table in the app has a field for the company, so you only see the information associated with that company. A user is given permission to only be in that company or companies.
I disdain the use of folders other than the ONE(there are occassions where this is different). Also if you make changes in anything, you have to make changes to all the folders. A simple field rule change may have to be done many times? Add a field to a table and have to do it many times??
If you make one set of tables(properly done), it will be more simple all the way around and solve many future headaches.
I have in most tables:
user name
company number
company table has all info about each company and a user may be associated with more than one company or not. I also have permissions set up so a user may only be able to view certain areas, but cannot add or edit anything.
It takes a little longer initially, but sure saves a lot in the end.
Reason why I did it is: bills ford may also be Bills hyundai and 5 other dealerships with seperate records. a salesperson may work from 3 of the stores and the f&I may work at 1-2 or 7 of the stores. They may all be within and area of 1 big city block. They may also have a used car operation 5 miles down the road. Bills used cars has to be added. Each can pull it's own reports and the owner can also. The owner can pull combined reports from the whole operation. That is 8 complete dealerships?
Everything is in one server at one dealership that is backed up 1 time a day onto a cd. It is also sent to an online server just in case.
My 2 cents is cast.
. -
Re: Many tables in database
Thanks Dave! I was not sure about having many tables like that but to A5 a table is a table. All I have to do is keep the the names straight! Thanks again!Comment
-
Re: Many tables in database
Check it out!Originally posted by webspinner View Post
http://www.aimsdc.net/Tips_Funcs/A5_...onventions.htm
Cal gives some good advice.Comment
-
Re: Many tables in database
Unlike Cal(I respect his ability and methods to the highest), I prefer no underscores and short definitive names in any place I use them. I do not expect my end user to see the names of fields or (in most cases)forms/reports/etc. I should be the only one keeping track of those.
I am not sure you got the point above about the not duplicating tables for each customer. I would not! I would only have one set of tables that would hold all client data if it is possible. It is possible either way. Of course, use your own judgement.
Where possible, I would use one table for summarized/collected or derived info. It is simpler. In other words, instead of making a table with tax return info and another for something else. I would make one table with the ability for all.
like
coid = company id
dateinp = date intered or appropriate
field(s) for taxes =
field(s) for other =
I have one table that holds Insurance agents, Gap companies, Warranty companies, banks for lrnding, Banks for payoff, and a little more. None of these are really related but many tables is a lot harder for me.
Good Luck
.Comment
-
Re: Many tables in database
The other point about using fewer tables is that if the system is networked, the load time on the workstation will become increasingly longer as the number of tables grows, even in a shadowed environment. Response time could become an issue.
TomComment
-
Re: Many tables in database
May I say, as above. I don't care for underscores. I never use spaces either. Prefering very short names all the way through the app.
Invoice report is better as invoice_report and even better as invorpt. My opinion only.
Heed Cal's and other's(mine too) advice in most any case, then, go do what you want.
Good luck.
.Comment
Comment