Normalized?

[Fish218]

I've been working on normalization of my db (see attached relationship table). Am I on the right track? This is my first db so I'm learning as I go. Thanks for any suggestions!

[June7]

Looks like a good start.

You might consider that the lookup tables don't have to use Autonumber field as the PK. Could just use the 'real world' codes as PK, especially if they are short. For instance, tblGearType has only the TypeID and Type fields. Type should be unique in this table, it could be the PK. That way these tables provide a standardized set of values that users can select from but by saving the more informative code value in tblCommGearCode will be easier to read. I NEVER set lookups in tables. When I view tables I want to see the actual value. Developers don't allow users to work directly with tables and queries, forms and reports are for data interface. Are you the only user of this db?

At least the Type field should be set as Index Yes no duplicates.

Here is a reference for standards to aim for http://access.mvps.org/access/tencommandments.htm
I will admit I have broken a few in order to accomplish what I want.

[Fish218]

There will be multiple people entering data on the database, but I will probably be the only one doing data analysis from it. If I switch my primary key to text, can it have spaces (e.g., Trawl Net)?

[June7]

Yes, it can. Should avoid special characters like & $ % # @ * " .

[Fish218]

I changed my PKs for the "Code" tables to text, updated the relationships, and updated tblCommGearCode. I now get an error message stating "You cannot add or change a record because a related record is required in table "tblFishSppCode". Referential Integrity issue?

[June7]

Yes, changing PKs can be hazardous and must be done carefully. I hope you didn't decide to do it just because I said you could but that you thought it was what you wanted. It sounds you took the correct steps. Did you make the SpeciesCode field the PK in tblFishSppCode? What Jointype did you set the relationships to? Do you want to provide project for analysis?

[Fish218]

I changed the PK because I have so many code tables it would be much more informative to look at a table and see Alewife Trap Nylon Bottom than 32 3 2 1 and becuase the values in those tables are unique identifiers for each table. I changed the PK in tblFishSppCode and joined with a one-to-many relationship.

[June7]

Okay, still sounds like that should work. Guess I will have to see the project if you want to provide.

[Fish218]

I figured it out. I had the "bound column" in my form combo box still set to 1 (the Autonumber) rather than the column containing text.

[June7]

Always the little things! Even if you still have the Autonumber field in table, no need for it in the RowSource.