How do I normalize these 3 specific columns?

Question

0.00/5 (No votes)

See more:

I am working on trying to develop a genealogy program. During this process, I have run into these issues dealing with the place of birth.

Here are the columns I have:
Place_of_birth
State_of_birth
County_of_birth

however, not all places have states and counties. this was one issue I am dealing with. another issue is if I strictly use place_of_birth as a single column then I run into using commas. For example, the person was born in Baltimore, MD. I remember reading that you should not use commas in your database table.

What I have tried:

I have thought that maybe I could divide this up by county and have separate tables for how they do their births. However, I am not sure if that is an efficient way to handle this problem.

Posted 13-Sep-18 15:42pm

Member 11856456

Updated 13-Sep-18 22:13pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2018-09-13T22:13:00

I've never been to the state. However, that doesn't stop me from doing a bit of research and these are what I found:
1. According to Independent city (United States) - Wikipedia[^]

Quote:
The U.S. Census Bureau uses counties as its base unit for presentation of statistical information, and treats independent cities as county equivalents for those purposes.

2. As such, every place in the states has a unique ST/CTY code as shown in STATE/COUNTY FIPS CODE NUMBERS WITH ASSIGNED MSA NUMBER - OWCP - U.S. Department of Labor[^]
Armed with this knowledge:
1. Create a look-up table say 'place' that contains three columns - st_cty, state_name, and county_name - where st_cty is the primary key
2. Your original table should have a column say 'place_of_birth' that references the st_cty in that 'place' table as foreign key.

Super Lloyd · Accepted Answer · 2018-09-13T17:41:00

normalize is different. it's mostly about avoid duplicated info.

at any rate you could very well have comma in your text, it's just that if you pass inline user input inline in your queries this will cause problem, i.e:

var uName = "lloyd, "<br />
...<br />
var sql = "select * from user where name like '" + uNname + "'";

which you solve by NEVER having inline user input, instead use SQL query parameter

var cmd = new SqlCommand("select * from user where name like $user");<br />
cmd.Parameters.Add("user", "lloyd, ");

How do I normalize these 3 specific columns?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0