I have just inherited a database with, what I consider, to be a pretty bad design. I'm eventually going to migrate it to a SQL server but before that I need to get it cleaned up.
There are only two tables "Head of Household" and "Family" I will list out the fields.
Head of Household
Code:
ID
Last Name
First Name
MI
SS#
Sex
Age
Birthdate
Street
City
State
Zip
Phone
# in Family
Monthly Income
IncAmt1
IncType1
IncFrq1
IncAmt2
IncType2
IncFrq2
IncAmt3
IncType3
IncFrq3
Family
Code:
ID
Last Name
First Name
MI
SS#
Sex
Age
Birthdate
IncAmt1
IncType1
IncFrq1
IncAmt2
IncType2
IncFrq2
IncAmt3
IncType3
IncFrq3
What I'd like to do is have one table that holds the household info, another that holds the members(with a field that signifies head), and a final one for incomes. I have been trying different ways with some dummy data but I cannot seem to find an efficient way to do this. The table analyzer doesn't work like I thought it would. Any suggestions on how I should approach this?