如何规范化这3个特定列？ [英] How do I normalize these 3 specific columns?

问题描述

我正在努力开发家谱计划。在这个过程中，我遇到了处理出生地的这些问题。



这是我的专栏：

Place_of_birth < br $>
State_of_birth

County_of_birth



然而，并非所有地方都有州和县。这是我正在处理的一个问题。另一个问题是，如果我严格使用place_of_birth作为单个列，那么我会遇到使用逗号。例如，这个人出生在马里兰州的巴尔的摩。我记得读过你不应该在你的数据库表中使用逗号。



我尝试了什么：



我原以为或许我可以按县划分这个，并为他们的出生方式分别制作表格。但是，我不确定这是否是解决此问题的有效方法。

解决方案

我从未去过州。然而，这并没有阻止我做一些研究，这些是我发现的：

1.根据独立城市（美国） - 维基百科 [ ^ ]

引用：

美国人口普查局使用县作为其基本单位提供统计信息，并将独立城市视为县级用于此目的。

2.因此，州内的每个地方都有一个唯一的ST / CTY代码，如州/县FIPS代号与指定的MSA号码 - OWCP - 美国劳工部 [ ^ ]

有了这些知识：

1.创建一个查找表，说'place'包含三列 - st_cty，state_name和county_name - 其中st_cty是主键

2.原始表应该有一个列'place_of_birth'，它引用该'place'表中的st_cty作为外键。

normalize是不同的。它主要是关于避免重复的信息。



无论如何你可以在文本中使用逗号，只是如果你在查询中内联传入内联用户输入会引起问题，即：

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

你通过内联用户输入来解决，而不是使用SQL查询参数

 var cmd = new SqlCommand（select * from user where name like 

user）;< br />
cmd.Parameters.Add（user，lloyd，）;

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.

解决方案

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.

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, ");

这篇关于如何规范化这3个特定列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！