3nf table structure, I think, no what?

3nf table structure, I think, now what?

I have been reading up on normalizing table structures. I have this database that I am working on, to work as a hiring database. It should hold information about people applying to the law firm I work at.

I am looking for advice on if this table structure is normalized and sound. I am also looking for specific help on creating good forms.

Thanks for any help that can be given!

ps. I am attaching a zip that has a jpg view of the tables and their relationships, as well as the db with the table structure.

I would love any and all advice. I want a very clear concept for this db before I do anything!

Thanks again!

Unfortunately, my site rules disallow downloads from the general Internet, so I cannot look at your files.

Basically, a little desk time (or board time) is always useful when trying to decide whether what you have is viable. Some folks need to visualize what they have and the table structure of Access doesn't always help to see the inner workings.

Get yourself a dry-erase board and a big pack or box of sticky notes. Write the table names on the board. Write stuff on some sticky notes that will represent table contents for each of your tables. Draw differently colored lines for your relationships.

Now for every operation you wish to support, perform that operation on the board. See how everything interrelates. Look at data flow. Your business rules will define when and how a particular action is permitted to occur. Like, you can't add a new employee without approval from someone, so perhaps you need a record of the approval process. (And a table to hold the date, approver, and potential employee info.) You can't fire an employee without a record of the disciplinary decision leading to that action. Etc. etc. etc.

This visual analysis step will help you to visualize the operations required for your business model. Since we can't see your business model, our advice is necessarily somewhat generic no matter how much experience we might have in personnel DBs.

I will say this: Studying 3NF before you start was a REALLY GOOD IDEA.

Well, I appreciate the post. I think, as I have started to create a form with subforms based on the table flow, that I have one main confusion. I have a table for addresses - tblAddress. This has the addressid (pk), candid(fk) address1(text), address2(text), cityid(lookup to tblcity).

tblcity has cityid(pk), city(text), stateid(lookup to tblstate), zip(number).

tblstate has stateID(pk), state, stateabbr.

The blank expression and look of complete confusion is because I don't understand how with an address subform on a candidate entry form, I can enter in a complete address. I need a city,state zip combo that may not be in the city table. How is this supposed to work for an end user?

Can you maybe help with the confusion here? my brain hurts!

Thanks for the suggestions!