I can see you've been studying normalization rules. I will try to answer your question later but I have to make a comment on your structure and to consider this:
Having 1 tblObservationDetails table with all the text fields in that 1 table (such as Area, Department, First Name, LastName, Sub-Area, etc..) with some reduncy in data
Multiple tables with multiple fields (ie. ID fields and the Text fields) plus having to link all the tables together each time to get all the data.
Although you've followed rules of normalization, I hate to see a linked table with just 1 or 2 texts field in it with these kind of one-to-many relationships. If you were to have a few million records (as an extreme), a query to return all the fields would be a lot slower than having 1 table with all the text fields in that 1 table plus it makes it more difficult working with multiple tables for data entry versus 1 table. And I only bring this up because most of your linked tables only store 1 text field of data in them and the one-to-many is not many of the linked tables data to 1 tblObservationDetail record but the opposite.
Again, there will be many who will agree with your design but just consider returning ALL the fields for the tblObservationDetails and what it entails or entering 1 data record (6 linked tables, and storing 18 fields of data (including all the ID fields)) versus 1 Table with 6 text fields and some reduncy in data (which isn't necessarily a sin - I mean, after all, you are duplicating ID fields when you split out all the tables like you did). Just something to think about. For example, some people would split out an address into multiple tables (1 table for City, 1 for State, 1 for Zip, etc...) I personally wouldn't do this as the cost of putting all those tables together to return 1 simple address (or enter it) just isn't worth the complication in design. Consider if you had a few million records and I asked you to return all the Observation Details. You'd have to link 6 tables together to get all the data and the query would be a tad slower than if it were all in 1 table. Again, just something to think about.
Now if you had multiple (for example) Departments for 1 tblObservationDetail record, then I'd stick with your design but that's not the way you have it set up. You have 1 (for example) Department for multiple tblObservationDetail records (and the same for your other linked tables to the tblObservationDetails table). I might consider your design as more practical if you had multiple Observers, multiple Departments, multiple Areas, etc...for 1 tblObservationDetails record. (kind of like having a separate linked table for multiple customer phone numbers for 1 customer record.)
I personally, would have made it easier for query returns for your tblObservationDetails and gone the route of 1 table and a bit of reduncy in data versus all the linked tables but that's just me and I wanted to give you something to think about in your future designs.
I would've made my tblObservationDetails more like this:
Comments (although here I might split this as a linked table if I had multiple comments for 1 tblObservationDetail record.)
Although I may have 2 or more records which have the same Department, Area, etc...it's soo much easier for data returns/queries if you want all the fields.
But I don't fully know the works of this database and perhaps you've intentionally made all the tables separate with these types of relationships for some other purpose.
Last edited by pkstormy; 06-09-08 at 20:58.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)