数据库设计：从城市列表定义数据访问

I am trying to build an API that gives data on various cities.

我正在尝试构建一个API,用于提供各个城市的数据。

Currently, there is a table called APIPriv, which has the following columns:

目前,有一个名为APIPriv的表,其中包含以下列:

===================================================
  idApiPriv | idClient | Daily | Hourly | DevKey
===================================================
      1     |    23    |  'Y'  |  'N'   |[MD5 Hex]

idClient is a foreign key to the client table.

idClient是客户端表的外键。

Daily and Hourly are types of data - 'Y' means the client / developer has access to the data and 'N' means he / she doesn't.

每日和每小时是数据类型 - 'Y'表示客户/开发人员可以访问数据,'N'表示他/她不访问。

DevKey is an autogenerated MD5 string, provided to the developer, which is used as the developer key.

DevKey是一个自动生成的MD5字符串,提供给开发人员,用作开发人员密钥。

Now, for each developer having access to the API, the database also needs to store a list of cities associated with each developer. The developer will only recive data on these cities.

现在,对于每个有权访问API的开发人员,数据库还需要存储与每个开发人员关联的城市列表。开发人员只会回收这些城市的数据。

How do I define this in the database? The number of cities in a list is not fixed. A developer may have access to 5 cities, another to 10, another to 7.

如何在数据库中定义它?列表中的城市数量不固定。开发者可以访问5个城市,另一个访问10个城市,另一个访问7个城市。

(There is already a master City table, which holds a list of 1500 cities and, naturally, the list of cities for each developer needs to be a subset of this data.)

(已有一个主城市表,其中包含1500个城市的列表,当然,每个开发人员的城市列表都需要是此数据的子集。)

4 个解决方案

#1

You'll need an associative table for the cities, with something to the effect of:

您需要一个城市的关联表,其效果如下:

 idDevCityPriv | idApiPriv | idCity
====================================
      1              1         34
      2              1         42

Then, to get your list of cities, it would go something like this (assuming you have one's DevKey):

然后,为了得到你的城市列表,它会是这样的(假设你有一个DevKey):

select
    c.*
from
    APIPriv a
    inner join DevCityPriv d on a.idAPIPriv = d.idAPIPriv
    inner join City c on d.idCity = c.idCity
where
    a.DevKey = [DevKeyVariable]

Cheers,
Eric

#2

Add another table that contains 2 columns - idClient and the id from the Cities table.

添加另一个包含2列的表 - idClient和Cities表中的id。

Then for each city that the developer has access to, add an entry into this table.

然后,对于开发人员可以访问的每个城市,在此表中添加一个条目。

#3

You will need another table with two columns one for the DevKey and one for the CityID. This will allow you to do a one to many join from the Developer to the Cities.

您将需要另一个表,其中两列用于DevKey,另一列用于CityID。这将允许您从开发人员到城市进行一对多的加入。

I would also suggest you change the Daily and Hourly to a boolean [true|false] rather than a char or maybe a bit [0|1]. These will lend themselves to logical conditions better and have a stricter set of possible values i.e. just two rather than 'y','Y','n','N','x' etc.

我还建议你将每日和每小时更改为布尔值[true | false]而不是char或者有点[0 | 1]。这些将更好地适应逻辑条件并具有更严格的可能值,即只有两个而不是'y','Y','n','N','x'等。

#4

I'd suggest what's called a join table, or link table:

我建议使用所谓的连接表或链接表:

DevKey       CityId
101          1 
101          7
101          4

This table defines which developers have access to which cities, and thus provides the basis of the many to many relationship between developer and city, which can't be modelled directly.

此表定义了哪些开发人员可以访问哪些城市,从而提供开发人员和城市之间的多对多关系的基础,这些关系无法直接建模。

You may want to hold some extra information in this table, for example role start and end date, whereby you can end date a developers access to a certain city.

您可能希望在此表中保留一些额外信息,例如角色开始日期和结束日期,从而可以结束开发人员访问某个城市的日期。

#1