Normalizing table

[carlpoppa]

Hello, again! I've been working on normalizing my table. I know it isn't done yet, but am I on the right track? I'm about to go into 2NF. Note: I know I shouldn't have spaces in between words. I'm currently just designing my database with Excel.

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

 

[gemma-the-husky]

can you explain what you are doing, and the table names you envisage requiring.

I find it easier to design the table layout first, and then accommodate the individual fields within the tables. (it's a bit of both really) - but I am really saying - given a "person table" you don't need to know that this will contain the name, address, DOB, sex, etc. If later on, you need to add something like the "place of birth", then it's clear that this belongs in this table. if you need to add "previous addresses" then equally you need another table (or a mvf) as a person can have more than one of these.

When you "load" the individual fields into the tables you have identified, you may find you need to redesign the table layout slightly. That's what I mean.

eg in your case, you shouldn't need separate tables for parents and children. Just one table. If they are related, then have another table to maintain the relationships, or you can even do this with suitable fields in the person table. eg if a given person has a mother field, and a father field, then any relationship is self evident. If the field is null, you do not have that particular parent within your database. it works the other way too. Given a female person, you can find all her children, just by selecting the people with the mother field equal to person under review. This relationship is automatically recursive - ie you can find grandchildren, by finding the children of the children.

 

[carlpoppa]

can you explain what you are doing, and the table names you envisage requiring.

I find it easier to design the table layout first, and then accommodate the individual fields within the tables. (it's a bit of both really) - but I am really saying - given a "person table" you don't need to know that this will contain the name, address, DOB, sex, etc. If later on, you need to add something like the "place of birth", then it's clear that this belongs in this table. if you need to add "previous addresses" then equally you need another table (or a mvf) as a person can have more than one of these.

When you "load" the individual fields into the tables you have identified, you may find you need to redesign the table layout slightly. That's what I mean.

eg in your case, you shouldn't need separate tables for parents and children. Just one table. If they are related, then have another table to maintain the relationships, or you can even do this with suitable fields in the person table. eg if a given person has a mother field, and a father field, then any relationship is self evident. If the field is null, you do not have that particular parent within your database. it works the other way too. Given a female person, you can find all her children, just by selecting the people with the mother field equal to person under review. This relationship is automatically recursive - ie you can find grandchildren, by finding the children of the children.

That really helps! Thank you! I'm actually working out of a book and they gave me the columns listed, which they wanted me to normalize (so I think DOB and all of those are required).

I've edited my Person table. Is this what you meant?

Person
-------
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

Person Relationship
--------------------
Person Relationship ID
Person ID - FK to Person
Spouse ID - FK To Person
Child ID - FK to Person

 

[informer]

Hi carlpoppa

here is a free soft to model: jfreesoft.com/JMerise/
conceptual data model (CDM)
Generate automatically ta Physical data model from the CDM

And to go further with normalization a wiki topic herehttpshttps://en.wikipedia.org/wiki/Database_normalization#Normal_forms

 

[Simon_MT]

This not being PC but when you think about it these are all individuals but it is there relationship to each other that is relevant.

So you could create a Junction table with Person ID, RType and RPerson ID. This could work both ways treating the partners equally within the structure - saves multiple tables essentially storing the same information - all your really need to do is to identify the relationship between the individuals.

Think about the issue - a couple who are together split up and other partner buys another car. So that Person may have been in your Spouse file and should be in the Person file. Then this person forms another relationship, and marries and under your regime then that person goes back into the Spouse table if the husband has a car too.

Believe you me if gets evden more complicated when art is concerned as the split the collection and if it goes up for sale you have to ensure the right owner is identified.

When you think about it A Car is Car, a Shop is Shop but the Owner may change. The model will be the same but the Year / Mileage / Color and perhaps Body Type are different. What happens if the spouse buys the car or someone else.

The circumstances now may not prevail later so it is better to build a little flexibility into structure.

Simon