I have a unique situation. Users of my site can submit articles for other users to review, however they can restrict who can review the articles by age and by country. My issue is that instead of having 250 records (1 for each country linking to the article) I thought it fitting to store all 250 countries (or whichever countries they want to be visible to other users) in JSON format in a text field in the database. This way I would only need one record per article. I'm not sure if the performance will suffer terribly? The site will handle between 1-2 million users and the number of articles submitted for review will be rather large as well. The only "processing" that would be done is each user's country is stored in the database and it would be checked against the country array for an article to see if that user is allowed to review that article.
我有一个独特的情况。我网站的用户可以提交供其他用户查看的文章，但是他们可以限制谁可以按年龄和国家/地区查看文章。我的问题是，我不认为有250条记录（每个国家链接到文章1条），我认为适合在JSON格式的文本字段中存储所有250个国家（或其他任何希望对其他用户可见的国家/地区）数据库。这样我每篇文章只需要一条记录。我不确定性能是否会受到严重影响？该网站将处理1-2百万用户，提交审查的文章数量也将相当大。将要完成的唯一“处理”是每个用户的国家/地区存储在数据库中，并且将针对国家/地区阵列检查文章以查看是否允许该用户查看该文章。
What do you guys think? Am I over-thinking 250 records for each article is a lot?
你们有什么感想？我是否过度思考每篇文章的250条记录是多少？
7 个解决方案
#1
4
I think that storing the data in a lookup table is perfectly acceptable. It gives you much more freedom in the future if something changes, and as long as you nicely index the tables, performance won't be sufffering too much.
我认为将数据存储在查找表中是完全可以接受的。如果发生变化，它将在未来为您提供更多*，并且只要您很好地索引表格，性能就不会太过分了。
Mysql easily handles data that is billions of records. Yes, you will need to ensure that you look after your data integrity - but adding a column to a lookup table versus changing an object that is stored in every single record suddenly seems much easier.
Mysql可以轻松处理数十亿条记录的数据。是的，您需要确保照顾数据完整性 - 但是在查找表中添加列而不是更改存储在每个记录中的对象突然变得更加容易。
Just make sure that you are keeping the data properly - as in you aren't repeating information that doesn't have to be repeated. Keep countries in one table, and a simple ID in the lookup table that references it.
只需确保您正确保存数据 - 因为您不会重复不必重复的信息。将国家/地区保存在一个表中，并在查找表中引用一个简单的ID来引用它。
#2
3
In short I would say that storing Json Data within a column in a Relational database is fine if you are not going to be querying data based on that column.
简而言之，我会说如果您不打算根据该列查询数据，那么将Json Data存储在Relational数据库的列中就可以了。
If you will be needing to look up data based on that column there would be a huge performance hit to having to parse the json prior to excluding data thus this would be a no no.
如果您需要根据该列查找数据，那么在排除数据之前必须解析json会有巨大的性能损失，因此这将是一个不可能。
We ran into this issue at my job on a smaller scale and storing json of the properties in the database has worked well to not increase the complexity of the database for non searched properties.
我们在我的工作中以较小的规模遇到了这个问题，并且在数据库中存储属性的json已经很好地工作，不会增加非搜索属性的数据库的复杂性。
#3
1
I would use another table instead for that data, and make a unique column to match it.
我会使用另一个表来代替该数据，并创建一个唯一的列来匹配它。
#4
1
You have a "country" table and a "article" table. I would make a third "country-article" consisting only of the indexes that should match. After all Mysql is relational. If you're worried about performance, benchmark.
你有一个“国家”表和一个“文章”表。我会制作第三个“国家文章”，其中只包含应该匹配的索引。毕竟Mysql是关系型的。如果你担心性能，基准。
#5
0
One option might be to use a bitfield to represent your countries, using the MySQL bit or binary type. This would allow you to store the information for each contry in a single bit, which would only end up requiring 32 additional bytes per record(8 bits/byte * 32 bytes = 256 bits).
一种选择可能是使用位域来表示您的国家/地区，使用MySQL位或二进制类型。这将允许您将每个contry的信息存储在一个位中，这最终只需要每个记录32个额外字节（8位/字节* 32个字节= 256位）。
I'm not certain, but it might even be possible to query using bitwise operations, which could potentially be very fast.
我不确定，但甚至可能使用按位运算进行查询，这可能非常快。
#6
0
You can have a separate table for Countries and have their IDs stored with the article table.
您可以为国家/地区设置单独的表，并将其ID与文章表一起存储。
You can have Options for All Countries, Asia, Europe, North America, South America etc. stored in your countries tables.
您可以在国家/地区表格中存储所有国家/地区，亚洲，欧洲，北美洲，南美洲等选项。
#7
0
JSON will prevent the DBMS from checking the validity of countries that you wish to store. It's basically an opaque text, so the DBMS cannot enforce referential integrity (foreign keys).
JSON将阻止DBMS检查您希望存储的国家/地区的有效性。它基本上是一个不透明的文本，因此DBMS不能强制引用完整性（外键）。
And even if you don't need to query on countries (which is a pretty big if), you'll at least need to parse JSON before checking for a particular country.
即使你不需要查询国家（这是一个非常大的问题），你至少需要在检查特定国家之前解析JSON。
JSON can be a good match for hierarchical data, but this is just a simple set (a country is either element of the set or not), which can be nicely represented by a separate junction table ARTICLE_COUNTRY, which can then be maintained and searched efficiently:
JSON可以很好地匹配分层数据，但这只是一个简单的集合（一个国家或者是该集合的元素），可以通过单独的联结表ARTICLE_COUNTRY很好地表示，然后可以有效地维护和搜索：
This junction table would link only to countries in which the article is accessible. If most articles are accessible from most countries, you could even reverse the meaning of the junction table and only store "forbidden" countries, thus lowering the overall number of rows.
此联结表仅链接到可以访问该文章的国家/地区。如果大多数国家/地区都可以访问大多数文章，您甚至可以撤消联结表的含义，只存储“禁止”国家/地区，从而降低总行数。
#1
4
I think that storing the data in a lookup table is perfectly acceptable. It gives you much more freedom in the future if something changes, and as long as you nicely index the tables, performance won't be sufffering too much.
我认为将数据存储在查找表中是完全可以接受的。如果发生变化，它将在未来为您提供更多*，并且只要您很好地索引表格，性能就不会太过分了。
Mysql easily handles data that is billions of records. Yes, you will need to ensure that you look after your data integrity - but adding a column to a lookup table versus changing an object that is stored in every single record suddenly seems much easier.
Mysql可以轻松处理数十亿条记录的数据。是的，您需要确保照顾数据完整性 - 但是在查找表中添加列而不是更改存储在每个记录中的对象突然变得更加容易。
Just make sure that you are keeping the data properly - as in you aren't repeating information that doesn't have to be repeated. Keep countries in one table, and a simple ID in the lookup table that references it.
只需确保您正确保存数据 - 因为您不会重复不必重复的信息。将国家/地区保存在一个表中，并在查找表中引用一个简单的ID来引用它。
#2
3
In short I would say that storing Json Data within a column in a Relational database is fine if you are not going to be querying data based on that column.
简而言之，我会说如果您不打算根据该列查询数据，那么将Json Data存储在Relational数据库的列中就可以了。
If you will be needing to look up data based on that column there would be a huge performance hit to having to parse the json prior to excluding data thus this would be a no no.
如果您需要根据该列查找数据，那么在排除数据之前必须解析json会有巨大的性能损失，因此这将是一个不可能。
We ran into this issue at my job on a smaller scale and storing json of the properties in the database has worked well to not increase the complexity of the database for non searched properties.
我们在我的工作中以较小的规模遇到了这个问题，并且在数据库中存储属性的json已经很好地工作，不会增加非搜索属性的数据库的复杂性。
#3
1
I would use another table instead for that data, and make a unique column to match it.
我会使用另一个表来代替该数据，并创建一个唯一的列来匹配它。
#4
1
You have a "country" table and a "article" table. I would make a third "country-article" consisting only of the indexes that should match. After all Mysql is relational. If you're worried about performance, benchmark.
你有一个“国家”表和一个“文章”表。我会制作第三个“国家文章”，其中只包含应该匹配的索引。毕竟Mysql是关系型的。如果你担心性能，基准。
#5
0
One option might be to use a bitfield to represent your countries, using the MySQL bit or binary type. This would allow you to store the information for each contry in a single bit, which would only end up requiring 32 additional bytes per record(8 bits/byte * 32 bytes = 256 bits).
一种选择可能是使用位域来表示您的国家/地区，使用MySQL位或二进制类型。这将允许您将每个contry的信息存储在一个位中，这最终只需要每个记录32个额外字节（8位/字节* 32个字节= 256位）。
I'm not certain, but it might even be possible to query using bitwise operations, which could potentially be very fast.
我不确定，但甚至可能使用按位运算进行查询，这可能非常快。
#6
0
You can have a separate table for Countries and have their IDs stored with the article table.
您可以为国家/地区设置单独的表，并将其ID与文章表一起存储。
You can have Options for All Countries, Asia, Europe, North America, South America etc. stored in your countries tables.
您可以在国家/地区表格中存储所有国家/地区，亚洲，欧洲，北美洲，南美洲等选项。
#7
0
JSON will prevent the DBMS from checking the validity of countries that you wish to store. It's basically an opaque text, so the DBMS cannot enforce referential integrity (foreign keys).
JSON将阻止DBMS检查您希望存储的国家/地区的有效性。它基本上是一个不透明的文本，因此DBMS不能强制引用完整性（外键）。
And even if you don't need to query on countries (which is a pretty big if), you'll at least need to parse JSON before checking for a particular country.
即使你不需要查询国家（这是一个非常大的问题），你至少需要在检查特定国家之前解析JSON。
JSON can be a good match for hierarchical data, but this is just a simple set (a country is either element of the set or not), which can be nicely represented by a separate junction table ARTICLE_COUNTRY, which can then be maintained and searched efficiently:
JSON可以很好地匹配分层数据，但这只是一个简单的集合（一个国家或者是该集合的元素），可以通过单独的联结表ARTICLE_COUNTRY很好地表示，然后可以有效地维护和搜索：
This junction table would link only to countries in which the article is accessible. If most articles are accessible from most countries, you could even reverse the meaning of the junction table and only store "forbidden" countries, thus lowering the overall number of rows.
此联结表仅链接到可以访问该文章的国家/地区。如果大多数国家/地区都可以访问大多数文章，您甚至可以撤消联结表的含义，只存储“禁止”国家/地区，从而降低总行数。