数据库设计困境。指导还是整数？
我目前有下表，
Nation
----------------
Id (int) primary key autoincrement
Name (char)
City
----------------
Id (int) primary key autoincrement
NationId (int) references Nation(Id)
Name (char)
Nation table data
Id Name
----------
0 England
1 USA
City table data
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
上面是我想要重新分发给我的应用程序的用户的数据库的一部分。
我的应用程序的用户将被允许添加自己的国家和城市。当他们添加自己的城市时，根据当前的数据库设计，显然会增加 City.Id 主键值。
但是，我希望在某个阶段允许用户能够从彼此的数据库中“推送”或“拉出”国家/城市。例如。 User1 在其城市表中具有附加值：
Id NationId Name
-----------------------
4 1 California
5 1 Chicago
而 User2 在其城市表中具有以下内容：
Id NationId Name
-----------------------
4 1 Houston
5 1 Phoenix
每个人都希望在其城市表中包含彼此的城市。一旦每个人都推送了不同的城市数据，他们的 City.Id、City.Name 属性就不会完全对应。
User1：
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
4 1 California
5 1 Chicago
6 1 Houston
7 1 Phoenix
User2：
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
4 1 Houston
5 1 Phoenix
6 1 California
7 1 Chicago
我希望 City.Id、City.Name 属性在所有用户数据库中具有相似的外观。
那么我应该选择 City.Id 的用户 guid 吗？或者有谁知道我可以实现这种所需同步的替代方法。
I currently have the following tables
Nation
----------------
Id (int) primary key autoincrement
Name (char)
City
----------------
Id (int) primary key autoincrement
NationId (int) references Nation(Id)
Name (char)
Nation table data
Id Name
----------
0 England
1 USA
City table data
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
The above is part of a database that I would want to redistribute to users of my application.
The users of my application will be allowed to add their own nations and well as cities. When they add their own city, as per the current database design, it would obviously increment the City.Id primary key value.
However, I would want to at some stage, allow users the ability to 'push' or 'pull' nations/cities from each other's database. For example. User1 has the additional value in his city table:
Id NationId Name
-----------------------
4 1 California
5 1 Chicago
While User2 has the following in his city table:
Id NationId Name
-----------------------
4 1 Houston
5 1 Phoenix
Each wants to have each other's cities in their city table. Once each has pushed their different cities data across, their City.Id, City.Name attributes won't quite correspond.
User1:
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
4 1 California
5 1 Chicago
6 1 Houston
7 1 Phoenix
User2:
Id NationId Name
-----------------------
0 0 London
1 0 Manchester
2 1 New York
3 1 Boston
4 1 Houston
5 1 Phoenix
6 1 California
7 1 Chicago
I would prefer to have the City.Id, City.Name attributes to have a similar look across all the users databases.
So should I rather user guid's for the City.Id? Or does anyone know of an alternative method I can achieve this desired synchronisation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
指南肯定可以解决您描述的问题，但是，当两个用户将 Boston 添加到他们的数据库时会发生什么？您最终会得到大量重复项 - 尤其是在常见城市。
您不能在数据库中预先填充城市信息吗？
Guids would certainly work for the problem you describe, however, what happens when two users add Boston to their databases? You'll end up with a large number of duplicates - especially in common cities.
Could you not pre-populate the database with city information?