Hello All,
Im designing a database for my group at work (my first...if you cant tell). A very short background:
- We do environmental regulation for the state and issue 6 different types of permits
- The log that tracks each permit type has shared fields but also a few unique fields
- The "Permit Number" for each permit type has a different structure (eg. 01501, P012349, IP-0148, etc.)
My thinking was to have separate tables to control the structure of the permit number through validation rules. However, this can be handled other ways.
Right now I have a table for each permit type with supporting tables for each containing data for Notes, History, and Reporting. I chose to have separate supporting tables for each permit type because there are instances where the permit number will be amended with an "A-Z" and I wanted the change to cascade to the supporting tables. The tables are updated directly via individual forms.
At this point, I am feeling that it may have been a better idea to have a unified table for Permits, Notes, History, and Reporting and to have the forms connected to queries instead of the tables directly (easier for reporting and certainly a lot cleaner looking). This was my first shot at a full database, and Ive learned a lot in the process. The database is not in use yet, so Im thinking now is the time to make the change if it is necessary.
Can anyone offer advice on a better structure?
My thanks in advance