中间可选模型的基本数据库设计

I'm working on a project in which I'm having slight difficulties coming up with the design for a seemingly very simple scenario:

我正在开展一个项目,在这个项目中我遇到了一个看似非常简单的场景设计的困难:

user belongs to city which belongs to country, however, the city reference may be null while user must belong to a country nevertheless. In other words (in basic RoR model syntax),

用户属于属于国家的城市,但是,城市参考可以为空,而用户必须属于国家。换句话说(在基本的RoR模型语法中),

# class User < ActiveRecord::Base 
belongs_to :city
belongs_to :country
validates_existence_of :country

# class City < ActiveRecord::Base
has_many :users
belongs_to :country
validates_existence_of :country

# class Country < ActiveRecord::Base
has_many :users
has_many :cities

My problem with this super simple design is the fact that there is so much redundancy. As soon as a city is referenced by a user, the country reference can be extrapolated from it (in other words, since it is already referenced in the city table, it seems not-so-awesome to also reference it in the user table).

这个超级简单设计的问题在于冗余太多了。一旦用户引用了一个城市,就可以从中推断出国家/地区引用(换句话说,因为它已经在城市表中被引用,所以在用户表中引用它似乎并不那么令人敬畏) 。

3 个解决方案

#1

This is what happens when A (city) also uniquely identifies B (country), but A is optional while B is mandatory. Basically, Country is only added because City is optional while there is still a need to identify the country of each user.

当A(城市)也唯一地标识B(国家)时会发生这种情况,但A是可选的,而B是强制性的。基本上,只添加国家/地区,因为城市是可选的,而仍需要识别每个用户的国家/地区。

The idea of tying country and city together, may seem attractive because a city uniquely "identifies" a country, but: does it? Amsterdam is not just a city in the Netherlands you know.

将国家和城市联系在一起的想法可能看起来很有吸引力,因为一个城市独特地“识别”了一个国家,但是:是吗?阿姆斯特丹不仅仅是荷兰的一个城市。

Plus it carries the problem you already mentioned in your comment... what do you do with additional data; and listing countries as such now requires filtering them out of the country/city amalgamation.

此外,它还包含您在评论中已经提到的问题...您如何处理其他数据;并列出这样的国家现在要求将它们从国家/城市合并中过滤掉。

Your original design may feel redundant and data-wise it probably is, but logic-wise and requirement-wise it isn't. I would stick with it as it is very clear and reflects the requirements perfectly. And I would learn to live with the apparant redundancy. Any "solution" you may come up with to avoid the "redundancy", will likely just end up muddying the waters. Or will make defining queries in the future more difficult.

您的原始设计可能感觉多余且数据方面可能是,但逻辑方面和需求方面则不然。我会坚持,因为它非常清楚,完美地反映了要求。而且我会学会忍受令人难以置信的冗余。你可能想出的任何“解决方案”,以避免“冗余”,可能会最终混淆水域。或者将来更难以定义查询。

#2

This has the 'sql' tag for some reason, so here's how I'd do it in SQL (note there is referential integiry throughout and no NULLable columns):

由于某种原因,它有'sql'标签,所以这就是我在SQL中如何做到这一点(注意整个参考整合并且没有NULLable列):

CREATE TABLE Countries 
(
 country_code CHAR(3) NOT NULL UNIQUE
);

CREATE TABLE Cities 
(
 city_name VARCHAR(20) NOT NULL, 
 country_code CHAR(3) NOT NULL 
    REFERENCES Countries (country_code), 
 UNIQUE (country_code, city_name)
);

CREATE TABLE Users
(
 username CHAR(8) NOT NULL UNIQUE, 
 country_code CHAR(3) NOT NULL, 
 UNIQUE (country_code, username)
);

CREATE TABLE UsersCountries
(
 username CHAR(8) NOT NULL UNIQUE, 
 country_code CHAR(3) NOT NULL, 
 FOREIGN KEY (country_code, username)
    REFERENCES Users (country_code, username), 
 city_name VARCHAR(20) NOT NULL, 
 FOREIGN KEY (country_code, city_name)
    REFERENCES Cities (country_code, city_name)
);

Test data:

INSERT INTO Countries (country_code) VALUES 
('ITL'), 
('ESP');

INSERT INTO Cities (city_name, country_code) 
VALUES 
('Roma', 'ITL'), 
('Naples', 'ITL'), 
('Barcelona', 'ESP'), 
('Madrid', 'ESP');

INSERT INTO Users (username, country_code) VALUES 
('00000001', 'ESP'), 
('00000002', 'ESP'), 
('00000003', 'ITL'), 
('00000004', 'ITL');

INSERT INTO UsersCountries (username, city_name, country_code) 
VALUES 
('00000002', 'Madrid', 'ESP'), 
('00000004', 'Roma', 'ITL');

To be fair, most SQL coders will not have an aversion to using a NULLable column and will prefer all user's details to appear in one table. Assuming your SQL product (correctly) does not treat NULL as a value (for example MS SQL Server does not but MS Access does) then the following will work and is equivalent to the above structure (i.e. again referential integiry throughout despite the existence of NULLable columns):

公平地说,大多数SQL编码器都不会厌恶使用NULLable列,而是希望所有用户的详细信息都出现在一个表中。假设您的SQL产品(正确)不将NULL视为值(例如,MS SQL Server不会将MS Access视为值),则以下内容将起作用,并且等效于上述结构(即,尽管存在NULLable,但仍然是整个引用整合列):

CREATE TABLE Users
(
 username CHAR(8) NOT NULL UNIQUE, 
 city_name VARCHAR(20), 
 country_code CHAR(3) NOT NULL
    REFERENCES Countries (country_code), 
 FOREIGN KEY (country_code, city_name)
    REFERENCES Cities (country_code, city_name)
);

INSERT INTO Users (username, city_name, country_code) VALUES 
('00000001', NULL, 'ESP'), 
('00000002', 'Madrid', 'ESP'), 
('00000003', NULL, 'ITL'), 
('00000004', 'Roma', 'ITL');

#3

I have no thoughtful answer, but first comes to my mind is this,

我没有深思熟虑的答案,但首先想到的是这个,

# class User < ActiveRecord::Base 
belongs_to :country, :through => :city
validates_existence_of :city

# class City < ActiveRecord::Base
has_many :users
belongs_to :country
validates_existence_of :country

# class Country < ActiveRecord::Base
has_many :users, :through => :city
has_many :cities

The trick is that a dummy or blank city is added to each country so that the validations hold.

诀窍是为每个国家添加一个虚拟或空白城市,以便验证成立。

#1