Normalizing a database for the first time.

[carlpoppa]

Hi, I've been teaching myself how to normalize. I know I still have some things to do (tables for phones), but I'm just wondering if I'm on the right track? Thanks.

How does my 1NF database look so far? I welcome any suggestions. Thank you.

PK Person ID
Person First Name
Person Last Name
Person Street
Person City
Person State
Person Zip Code
Person Country
Person SSN
Person Marital Status
Person DOB

FK Person ID

Phone Numbers
Type


FK Person ID
PK Spouse ID
Spouse First Name
Spouse Last Name
Spouse Street
Spouse City
Spouse State
Spouse Zip Code
Spouse Country
Spouse SSN
Spouse DOB
Spouse Marital Status

PK Person ID
PK Spouse ID
Child ID
Child First Name
Child Last Name
Child Street
Child City
Child State
Child Zip Code
Child Country
Child SSN
Child DOB
Child Marital Status

FK Person ID
Vehicle Make
Vehicle Model
Vehicle Year
Vehicle Color
Vehicle Mileage
Vehicle Body Type
PK Vehicle ID

PK Vehicle ID
Vehicle Repair Date
Vehicle Repair Description
Vehicle Repair Cost
PK Vehicle Repair ID

PK Vehicle Repair ID
Vehicle Repair Shop Name
Vehicle Repair Shop Street
Vehicle Repair Shop City
Vehicle Repair Shop State
Vehicle Repair Shop Zip Code
Vehicle Repair Shop Country
FK Repair Shop ID

PK Repair Shop ID
Repair Shop Phone Numbers
Repair Shop Owner First Name
Repair Shop Owner Last Name
Repair Shop Owner Street
Repair Shop Owner City
Repair Shop Owner State
Repair Shop Owner Zip Code
Repair Shop Owner Country
Repair Shop Owner Phone Numbers

[PhilOfWalton]

Without wishing to be rude you asked how it looks - horrible - I jest.
I good guide to normalisation is to see how often the same piece of data is going to appear. So for example addresses are going to be duplicated, triplicated etc. depending on the size of the family.
A second point is that a table should roughly contain information of a similar type, so with with your persons table, names, age, sex, hair colour, marital status all apply to a person. These items are "locked" to that person. Their address changes when they move home.

So a simple example: In the case of your families, I'm guessing that many of the children live at the same address as the parents, so the address only needs to be entered once.
Now do we need a complete address? How often does the same City appear in the addresses? Once you have defined a City, you know what State it is in.
The remarks about addresses apply equally to the repair shops.
So to give you a start:
TblStates
StateID PK
State No duplicates

TblCities
CityID PK
City
StateID FK

TblAddress
AddressID PK
AddressLine1
AddressLine2
AddressLine3
CityID FK

So at this point, once you have entered the city, the state comes up automatically.

TblStatus
StatusID PK
Status (No duplicates) - Head, Spouse, Son, Daughter, Granny etc.

TblPerson
PersonID PK
PersonFirstName
PersonLastName
PersonMaritalStatus
PersonDOB
StatusID Fk
PersonHeadID FK
AddressID FK

So here's the critical bit. If you set PersonHeadID = PersonID, then that is equivalent to your Person Table. Let's be terribly politically incorrect and call this person the "Head of House"
Your Spouse and Children tables just set the PersonHeadID = PersonID (of the Head of House). The StatusID defines what relationship that person has to the "Head of House"

One further point. I strongly advise you not to have spaces in field names. If you do, you will always have to put square brackets[] round them, as Access has no way of knowing when a field name ends. so [Person First Name]
Alternatives are
Person_First_Name
or PersonFirstName.
I personally prefer the latter.

Hopes this gets you stated

Phil

[zmbd]

@ carlpoppa,
Have you read thru our insights article:
Database Normalization and Table Structures
It will help explain things.

As PhilOfWalton notes, spaces are not your friend in the long run, nor are the special characters or reserved words. IMHO, and I dare-say a few others, it is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY important to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[jforbes]

As Phil and zmbd have metioned, it's much easier to write code without spaces in the field names than with spaces in the field names.

I would merge Person and Spouse tables into the same table and add a Foreign key for Spouse that links back to the same table. Depending on your needs you may need to add a Type field to determine if it is a Spouse or not.

Personally, I would not have a separate PhoneNumber Table. I would create three PhoneNumbers fields in the Person table along with three PhoneType fields and default the PhoneTypes to Cell. This will make entering, displaying, and reporting data much easier as you won't have to Link to an additional table.

Lastly, unless I'm misunderstanding what it is that you are doing, I think the Vehicle, VehicleRepair and RepairShop tables are messed up a bit. I think you will want something like this, where there's a Vehicle Table and a RepairShop Table and then a VehicleRepair Table that links the two tables together:

Vehicle
VehicleID (PK) 
Make
Model
Year

RepairShop
RepairShopID (PK)
ShopName
Address1
Address2
City
State
ZipCode

VehicleRepair
VehicleRepairID (PK)
VehicleID (FK)
RepairShopID (FK)
RepairDate
Description
Cost

[PhilOfWalton]

Sorry to disagree with you, jforbes.
Firstly I think (as indicated) that Person, Spouse & Child table should all be one table. The only difference between the three groups is their status (Head, Spouse, Son, Daughter Grandmother, etc) and who is the head of that group.
Secondly I see no reason for the RepairShop table. It's just an address. You may want a table of

AddressTypes
    AddressTypeID
    AddressType   (Main Home, Holiday Home, Repair shop etc)
 

and obviously a suitable FK AddressTypeID in the address table.
This would then give carlpoppa the option to hold multiple addresses for each person using a join table.

Phil

[carlpoppa]

Thanks to everyone for the information. You all have definitely helped me understand this a lot better now.