Idea needed - mapping/relating two tables together
I'm helping my nephew with a project and I gave him the idea below, but want to see if anyone has other better ideas.
I'll go step by step.
1. We have two forms; "Intake" and "iXYZ". These two forms have some fields in common. For example, Address, Nationality, Date of Birth
2. Form "Intake" is populated. We plan on using Amazon's Textract to extract this form and dump the data in a table
Ex:
Column 1 Column 2
--------------------------
Name Greg Someone
Date Of Birth 01/01/1966
Address 1234 Fake Street
Nationality USA
Referred By My friend
3. Form "iXYZ". We will parse it with Amazon's Textract and dump this in a table as well. This form is not populated.
Ex:
Column 1 Identifier Column
---------------- -------------------------
Family Name Name
First Name
Street Number and Name Address
Date Of Birth Date of Birth
Country of Citizenship or Nationality Nationality
4. I want to see which fields in "iXYZ" form related to form "Intake". So, I created the "Identifier column" and populated it with the values you see.
5. I will have Python or C# code looking at these tables and based on the "identifier column"...I'll populate form "iXYZ" with the values from form "Intake".
Good design? any other ideas? thanks
I'll go step by step.
1. We have two forms; "Intake" and "iXYZ". These two forms have some fields in common. For example, Address, Nationality, Date of Birth
2. Form "Intake" is populated. We plan on using Amazon's Textract to extract this form and dump the data in a table
Ex:
Column 1 Column 2
--------------------------
Name Greg Someone
Date Of Birth 01/01/1966
Address 1234 Fake Street
Nationality USA
Referred By My friend
3. Form "iXYZ". We will parse it with Amazon's Textract and dump this in a table as well. This form is not populated.
Ex:
Column 1 Identifier Column
---------------- -------------------------
Family Name Name
First Name
Street Number and Name Address
Date Of Birth Date of Birth
Country of Citizenship or Nationality Nationality
4. I want to see which fields in "iXYZ" form related to form "Intake". So, I created the "Identifier column" and populated it with the values you see.
5. I will have Python or C# code looking at these tables and based on the "identifier column"...I'll populate form "iXYZ" with the values from form "Intake".
Good design? any other ideas? thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
If this were me I would transform the Textract to be what I needed it to be. in IXYZ and just go off of one table.This violates any given database normalization!! Please don't publish bad practices ;-)
Ummm, no it doesn't.
You take data from a source, transform it into 1 table, and then use said data in a different form.
Where is normalization violated?
You take data from a source, transform it into 1 table, and then use said data in a different form.
Where is normalization violated?
What happens if 2 people live at the same address?! Yes, bingo, you'll have redundant data! Thus -> no normalization!
So if 2 people have the same birthday, do you have a birthdayID and store that in a different table as well? The proposed table is 3NF compliant.
Yes, you can split it off if you wanted to get more complicated but there's no need.
Yes, you can split it off if you wanted to get more complicated but there's no need.
So if 2 people have the same birthday, do you have a birthdayID and store that in a different table as well?Don't be silly ;-)
But regarding the address portion, you'll have more than just one column to connect to the person, like zip code, street, house number, po box etc...
And this has absolutely nothing to do with "getting more complicated"!! Those comments always come from people who never did a proper database design! Period!
ASKER