Need help with normalization of Access database design

[chelonidae]

Based on feedback from my thread about forms here, I opened a this new thread about normalization since it deviated from my original topic.



I inherited a database that doesn't meet the first normal form (designed like spreadsheets). I'm trying to create a new database with the same information, but am stuck as to how to group some of the data and when to use look-up tables as opposed to storing fields as data in a smaller table.

A key criteria for my users is to 1) use drop-down lists in forms (ie list boxes or combo boxes) whenever possible, and 2) to have the forms look like the datasheet (ie spreadsheet feel) so that data entry is fast.


Tackling the issues of normalization first. This is structure of former DB:

This is what I have now.

More background on what we are doing (if you are still with me, THANKS!):
We conduct "creel" surveys where we assess (clamming or crabbing) Effort and then later interview people to assess their catch. For effort, we count the number of people at many areas at a single port. The area, time, and counts are recorded. Later, we go to each area and interview people, where we count the number of clams (or crabs) of each target species and take other information. Our datasheets look like spreadsheets. To avoid entry errors for novice data entry users, I need forms that look a lot like these datasheets.

[CJ_London]

not sure if I can be much help, but I'll give you my thoughts.

tbl_effort: On the matter of lookup tables (i.e. ones with just an ID and a description) I take what I believe to be the practical view that unless the number of fields in that table could be expanded, the list is not enormous and the entries are rarely edited, just use the description as the primary key (as you have for lu_species in your lower image) and store the text in your table rather than an ID. Whether or not to enforce the relationship is then more a matter of ensuring the field in the main table is completed and cannot be modified (other than by selecting another value) whilst queries become simpler down the line because you don't need to link to the lookup table. If you do enforce referential integrity, make sure you also select cascade update related fields as well - so if you do correct a description, you only need to do it in the lookup table and the other tables will also be corrected. This has an overhead in that the indexing requirement will be greater (text takes up more space than numbers) particularly in your main table, so take a balanced view.

when a lookup is used in more than one main table (e.g. species is in interviews and biodata) drag the species lookup onto the relationship form twice, link one to interviews and the other to biodata


tbl_interviews: store the numbers in a separate table. Works in a form by using a subform. But why not store the number in tbl_biodata? but not sure what this table does anyway


tbl_biodata: not clear what 'only 1/record' means. If you mean it is a one to one relationship with interviews, combine the two tables

[chelonidae]

Thanks for your feedback, Ajax.

when a lookup is used in more than one main table (e.g. species is in interviews and biodata) drag the species lookup onto the relationship form twice, link one to interviews and the other to biodata

Is this different than just linking the one Species lookup to both tables? Is the mechanics different by copying it twice even though it is the same table?

tbl_interviews: store the numbers in a separate table. Works in a form by using a subform. But why not store the number in tbl_biodata? but not sure what this table does anyway

So something like tbl_SpeciesCounts? There are multiple species, each of which needs a value even if it is zero. Best practice? This has been my challenge; get rid of species as columns (since we may add or subtract in the future), even though that is the most intuitive and easy way to record these data.

tbl_biodata: not clear what 'only 1/record' means. If you mean it is a one to one relationship with interviews, combine the two tables

-tbl_Effort is counting the # people doing an activity (clamming or crabbing)
-tbl_Interviews include data when we count how many of each species these people have at each area. We call this their "catch" (ie how many they caught of each species)
-tbl_Biodata is collecting biological data, like weight and length, of a subset of some people's catches. It is different data, so a different table. There doesn't need to be data for all or any of the species.

[CJ_London]

Is this different than just linking the one Species lookup to both tables? Is the mechanics different by copying it twice even though it is the same table?

It's just the way I've always done it. Try it and see. You'll be creating a relationship loop which should be avoided, but since this is a lookup, it might not matter.

So something like tbl_SpeciesCounts?

probably, but don't see why you need to populate with zero, the absence of a record tells you the same thing

[chelonidae]

probably, but don't see why you need to populate with zero, the absence of a record tells you the same thing

Good point. I can probably design a query to have all the species represented and populated with zeros. The only caveat here is that it may not be in entered because it is zero or because it was not counted. A count of zero matters.

[chelonidae]

This is the new design. However, it seems like I have relationship loops. I'm using two lookup tables (luSpecies, luAreas) to serve as the options for different data tables. Is this a problem or should I get rid of the relationships (1 to many) for these two lookups. They are not redundant! For example, tbl_Effort surveys specific areas for # people. On the same or different date, I can take interviews of individual people at some of these same areas. Similarly, tbl_Catch and tbl_Biodata have information on different species (animals). Catch is the number and weight of all animals caught by a person. Biodata are measurements of single animals and these data are not always taken. Is this a problem? How to mitigate with design?

[CJ_London]

As I said before, since this is a lookup, it may not matter. the whole point around db design is to ensure it meets your needs. populate with some test data and see if it does.