Separate tables or a set of unified tables?

[fret hack]

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

[June7]

I vote for unified. Yes, the 'few unique fields' may be empty for a bunch of records but that is tolerable. It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.

If you use autonumber as primary key, 'cascading' the permit number change will not be a concern. Unless I misunderstand and you want to retain the previous number for historical purposes.

[fret hack]

Thank you for your advice. I was pretty sure that unification would be the answer, but it never hurts to ask.