I want my users to be able to specify their locations so that I can plot them on a map. Given an address, I use Google Maps API to get their lat/long coordinates and store that in the database.
我希望我的用户能够指定他们的位置,以便我可以在地图上绘制它们。给定一个地址,我使用Google Maps API获取他们的纬度/经度坐标并将其存储在数据库中。
Additionally, I want to allow users to search for other users based on location. Using Google Maps API, I can also get, say, country/state/city for address (or lat/long coordinates). My problem is that I don't know how to store country/state/city in such a way that:
此外,我希望允许用户根据位置搜索其他用户。使用谷歌地图API,我也可以获得国家/州/城市的地址(或纬度/经度坐标)。我的问题是我不知道如何以这样的方式存储国家/州/市:
- The data is associated to a particular user
- There is no data redundancy
数据与特定用户相关联
没有数据冗余
The problem I think is if User-1 and User-2 both enter an address that results in the country being "USA", I think I need to know that User-1 and User-2 are both from the USA -- AND that "USA" is only stored once in the DB.
我认为问题是如果User-1和User-2都输入导致该国家为“USA”的地址,我想我需要知道User-1和User-2都来自美国 - 而且“USA”仅在DB中存储一次。
When users search for other users, I think I should only let them search for users in the USA if I actually have users from USA. Meaning, assume User-1 and User-2 are the only 2 users from the USA, if User-1 and User-2 delete their profiles, I shouldn't allow searches for users in the USA anymore.
当用户搜索其他用户时,我想我应该只让他们在美国搜索用户,如果我实际上有来自美国的用户。这意味着,假设User-1和User-2是来自美国的唯一2个用户,如果User-1和User-2删除了他们的个人资料,我不应再允许在美国搜索用户。
Are my ideas conceptually wrong? In any case, how should I model this information? I'm using MySQL.
我的想法在概念上是错误的吗?在任何情况下,我应该如何建模这些信息?我正在使用MySQL。
1 个解决方案
#1
6
Your goals and intentions are correct (don't give them up!), you may need a bit of help getting over the line, that's all. The more understanding and experience you have with data modelling and Normalisation, the easier it will be. So do as much research and exercise as you can (SO or the web is not a good way to learn anything).
你的目标和意图是正确的(不要放弃!),你可能需要一些帮助来克服这一切,就是这样。您对数据建模和规范化的理解和经验越多,就越容易理解。所以尽可能多地进行研究和锻炼(因此,网络不是学习任何东西的好方法)。
-
For loading and maintenance purposes, you are better off ensuring that you have a normalised, top-down structure for geographic locations. You can load it from info provided (usually free) by your council or county or Post Office or whatever. That will eliminate manual data entry for the External Reference tables.
出于装载和维护目的,最好确保您拥有适用于地理位置的标准化自顶向下结构。您可以通过您的理事会,县或邮局提供的信息(通常是免费的)加载它。这将消除外部参考表的手动数据输入。
-
This is a highly Normalised Data Model, at 5NF.
这是一个高度标准化的数据模型,为5NF。
-
But there's more Normalisation that can be done; more duplication that can be removed. It is not worth it unless you are really interested, eg. you need to search on LastNames, etc.
.但是可以做更多的规范化;更多可以删除的重复。除非你真的感兴趣,否则它是不值得的,例如。你需要搜索LastNames等。
-
This one is for an Utility company, to ensure that false Street locations are not provided by the prospective customers.
这是一个公用事业公司,以确保潜在客户不提供虚假的街道位置。
-
Address is a specific house or unit (apartment), and that it not duplicated. Two People living at the same address will use one Address row.
地址是特定的房屋或单元(公寓),并且不重复。居住在同一地址的两个人将使用一个地址行。
-
This structure handles "any" geographic location. Note that some countries do not have State; some States do not have Counties; some Towns do not have Suburbs; etc. Rather than building all those exceptions into the Data Model, I have kept the hierarchy "clean". You will still need to handle that in your SQL (whether or not the model is simple with no exceptions, or whether it has exceptions; because that is the real world), and not display the State for a State-less Country. All you need is a row for the non-State with a StateCode of CHAR(0) that identifies the condition.
该结构处理“任何”地理位置。请注意,有些国家没有国家;有些国家没有县;一些城镇没有郊区;我没有将所有这些异常构建到数据模型中,而是将层次结构保持为“干净”。您仍然需要在SQL中处理它(无论模型是否简单,没有例外,或者它是否有例外;因为这是现实世界),而不是为无状态国家显示State。您只需要一个非State状态的行,其StateCode为CHAR(0),用于标识条件。
-
I have placed Longitude & Latitude at the Suburb level, assuming that that is what your users can choose easily via GoogleMaps, and because Street level will have limitation (might be to fine grained, and would cause duplication; or not fine grained enough for cities with very long Streets). Easy to change.
我已经在郊区级别放置了经度和纬度,假设这是您的用户可以通过GoogleMaps轻松选择的,并且因为街道级别将具有限制(可能是细粒度的,并且会导致重复;或者对于城市来说不够精细很长的街道)。易于改变。
-
For now, I suggest you do not worry about identifying users in the same country, first see if you can handle the SQL to identify users in the same Suburb (not Street, that is easy). After that, we can deal with City, County, Country, etc.
现在,我建议您不要担心在同一个国家/地区识别用户,首先看看您是否可以处理SQL来识别同一个郊区的用户(不是Street,这很容易)。之后,我们可以处理城市,县,乡等。
-
I think the other searches you identify are effortless; see if you agree.
我认为你认出的其他搜索是毫不费力的;看你是否同意
-
Anyway, this is just something to get you started; there is some interaction to be had before it is finalised.
无论如何,这只是让你开始的事情;在最终确定之前有一些互动。
Link to GLS Data Model (plus the answer to your other question)
链接到GLS数据模型(加上您的其他问题的答案)
Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.
链接到IDEF1X表示法,适用于那些不熟悉关系建模标准的人。
#1
6
Your goals and intentions are correct (don't give them up!), you may need a bit of help getting over the line, that's all. The more understanding and experience you have with data modelling and Normalisation, the easier it will be. So do as much research and exercise as you can (SO or the web is not a good way to learn anything).
你的目标和意图是正确的(不要放弃!),你可能需要一些帮助来克服这一切,就是这样。您对数据建模和规范化的理解和经验越多,就越容易理解。所以尽可能多地进行研究和锻炼(因此,网络不是学习任何东西的好方法)。
-
For loading and maintenance purposes, you are better off ensuring that you have a normalised, top-down structure for geographic locations. You can load it from info provided (usually free) by your council or county or Post Office or whatever. That will eliminate manual data entry for the External Reference tables.
出于装载和维护目的,最好确保您拥有适用于地理位置的标准化自顶向下结构。您可以通过您的理事会,县或邮局提供的信息(通常是免费的)加载它。这将消除外部参考表的手动数据输入。
-
This is a highly Normalised Data Model, at 5NF.
这是一个高度标准化的数据模型,为5NF。
-
But there's more Normalisation that can be done; more duplication that can be removed. It is not worth it unless you are really interested, eg. you need to search on LastNames, etc.
.但是可以做更多的规范化;更多可以删除的重复。除非你真的感兴趣,否则它是不值得的,例如。你需要搜索LastNames等。
-
This one is for an Utility company, to ensure that false Street locations are not provided by the prospective customers.
这是一个公用事业公司,以确保潜在客户不提供虚假的街道位置。
-
Address is a specific house or unit (apartment), and that it not duplicated. Two People living at the same address will use one Address row.
地址是特定的房屋或单元(公寓),并且不重复。居住在同一地址的两个人将使用一个地址行。
-
This structure handles "any" geographic location. Note that some countries do not have State; some States do not have Counties; some Towns do not have Suburbs; etc. Rather than building all those exceptions into the Data Model, I have kept the hierarchy "clean". You will still need to handle that in your SQL (whether or not the model is simple with no exceptions, or whether it has exceptions; because that is the real world), and not display the State for a State-less Country. All you need is a row for the non-State with a StateCode of CHAR(0) that identifies the condition.
该结构处理“任何”地理位置。请注意,有些国家没有国家;有些国家没有县;一些城镇没有郊区;我没有将所有这些异常构建到数据模型中,而是将层次结构保持为“干净”。您仍然需要在SQL中处理它(无论模型是否简单,没有例外,或者它是否有例外;因为这是现实世界),而不是为无状态国家显示State。您只需要一个非State状态的行,其StateCode为CHAR(0),用于标识条件。
-
I have placed Longitude & Latitude at the Suburb level, assuming that that is what your users can choose easily via GoogleMaps, and because Street level will have limitation (might be to fine grained, and would cause duplication; or not fine grained enough for cities with very long Streets). Easy to change.
我已经在郊区级别放置了经度和纬度,假设这是您的用户可以通过GoogleMaps轻松选择的,并且因为街道级别将具有限制(可能是细粒度的,并且会导致重复;或者对于城市来说不够精细很长的街道)。易于改变。
-
For now, I suggest you do not worry about identifying users in the same country, first see if you can handle the SQL to identify users in the same Suburb (not Street, that is easy). After that, we can deal with City, County, Country, etc.
现在,我建议您不要担心在同一个国家/地区识别用户,首先看看您是否可以处理SQL来识别同一个郊区的用户(不是Street,这很容易)。之后,我们可以处理城市,县,乡等。
-
I think the other searches you identify are effortless; see if you agree.
我认为你认出的其他搜索是毫不费力的;看你是否同意
-
Anyway, this is just something to get you started; there is some interaction to be had before it is finalised.
无论如何,这只是让你开始的事情;在最终确定之前有一些互动。
Link to GLS Data Model (plus the answer to your other question)
链接到GLS数据模型(加上您的其他问题的答案)
Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.
链接到IDEF1X表示法,适用于那些不熟悉关系建模标准的人。