Normalization / Table Design

[Larry in TN]

After reading the help from June7, and others, on Normalization I wanted to see if I'm now on the right track.

My application is a db to track my personal paychecks. I do this now in an Excel spreadsheet and am using it as a project to help learn Access. I was initially putting every line-item from each paycheck into a single table (one record per paycheck). It was pointed out that my structure wasn't normalized. After reading the supplied reference, messages, and the Normalization section in Access 2016 for Dummies I've restructured to the following.

tblPayChecks: One record per check. Includes the check date and employer/source.
tblCheckDetail: One record per line-item. i.e. the gross pay is one record, the federal withholding is another, etc.
tblTransTypes: Defines the different line items that can be on checks. i.e. "Gross Pay", "401k Contribution", etc. All of the fields are Yes/No fields except Description (Short Text - 20) and TransTypeID (AutoNumber).

I'm I on the right track?

I just noticed that I was inconsistent in naming my keys. I'll fix that...

Thanks.

[CJ_London]

would think you would need some extra fields in tblcheckdetail - such as who you are paying or being paid by and what for

also not clear from your description the benefit of tblpaychecks - your design implies one check, many amounts

Also 'Description' is a reserved word so you may want to change that

[pbaldy]

I would expect the trans types table to have 2 basic fields, a key and a description. You appear to have fields for each transaction type, rather than records in that table.

[ssanfu]

I agree with Paul; most of the fields you have listed in tblTransTypes are data. Right now tblTransTypes is designed as a spreadhsheet.

tblPayChecks: I would add a field for Check Number - The number on the check from the employer.- will be easier when searching.

I just noticed that I was inconsistent in naming my keys. I'll fix that...

I add a suffix to my PK/FK fields; makes it easier to distinguish:
tblPayChecks...............tblCheckDetails
--------------------------------------------------
PayCheckID_PK -------> PayCheckID_FK


.tblCheckDetails
----------------------
CheckDetailID_PK

tblTransTypes........tblCheckDetails
---------------------------------------
TransType_PK -----> TransType_FK

[Larry in TN]

Thanks for the replies...

Paycheck number: I considered that, and you'd normally have it included, but I have no need for it so left it out.

My tblTransTypes seems to be generating some confusion. Here's some sample data in the table to help illustrate what I'm trying to do.

The Earnings field determines if the item is a earnings or a deduction.
The Taxable field determines if earnings in that category are taxable and will be used for calculations later when estimating the tax withholding on future checks.
The FedTax and StateTax fields just identify those types of deductions and will be used for grouping them together on forms and reports.
The PreTax field identifies deductions that are pre-tax.

I didn't know how to track these attributes for each earning/deduction category and this was my first attempt. I'm now thinking of changing from Yes/No boxes to Number->Integer so that it can also be used to sort the items in each category to display in the desired order.

Here's sample data (one paycheck) in tblCheckDetail.

Here's sample data in tblPayCheck.

All it has is the ID, date, and employer (source).

The sample data includes the entire entry for a single paycheck.