This isn't a question per say but more of a best practice method for tables and databases.
I have been working with databases from well over 20 years now and everyone has their own style to creating and maintaining and upgrading and planning for growth.
Since I am almost 100% self taught I wanted to get the view of experts like yourselves.
Here is the scenario:
1) Upsizing to SQL is not an option and nor is using SharePoint
2) The database is in Microsoft Access 2010 format (ACCDB)
3) There is a front end and a back end
Here are the questions:
1) Should all look up tables be in a separate database?
2) Should all working data be in a separate database?
3) Should a memo field be in a separate table?
4) Should attachments be in a separate table?
5) Should the auto number be sequential or should it be random as far as ID goes?
6) Naming conventions:
a) Should two word be separated by an under score as in Last_Name or should it be one word; LastName
b) Should queries be used or should the use of VB be used to execute a query; Is there a performance difference
7) Performance: What are the best practices to get the most out of this type of a application?
The database I am working with has all the tables in one database; the backend. The front end holds the Queries, Forms, Reports, Macros and Modules. I am worried about the size. There is a lot of attachments and that has increased the size of the database to almost 500mb. I know Access can do 2Gb but I really want to avoid that.
Also if I did a separate database for the attachments how would maintenance be done on the database? Like compacting it?
I want the customer to be able to customize the database as much as possible like choosing the path for attachments and pictures and where the front end is stored. And be able to change as needed.
What are the best practices for fields?
I have a lot of books for Access and I have read them. I'm asking from others to increase my skill set and see what really works in practice. Some concepts work great in theory and in a lab but when they are fielded they seem to fall short. Like replication.