关系表的最佳方式 [英] Best way with relation tables

问题描述

我有一个关于表和关系表的问题...
其实我有这3张桌子

I have a question about tables and relations tables ...
Actually, I have these 3 tables

CREATE TABLE USER (
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(14) DEFAULT NULL
);

CREATE TABLE COUNTRY (
  ID int(11) NOT NULL AUTO_INCREMENT,
  COUNTRY_NAME varchar(14) DEFAULT NULL
);

CREATE TABLE USER_COUNTRY_REL (
  ID int(11) NOT NULL AUTO_INCREMENT,
  ID_USER int(11) NOT NULL,
  ID_COUNTRY int(11) NOT NULL,
);

好的，现在，1 个用户可以拥有一个或多个国家/地区，因此，表 USER_COUNTRY_REL 中的多个条目用于一个用户.
但是，我的表 USER 包含近 130.000 个条目...
即使对于 1 个国家/地区的用户，USER_COUNTRY_REL 表也几乎是 10Mo.
我有几个这种风格的相关表格......

Ok, so now, 1 user can have one or more country, so, several entries in the table USER_COUNTRY_REL for ONE user.
But, my table USER contains almost 130.000 entries ...
Even for 1 country by user, it's almost 10Mo for the USER_COUNTRY_REL table.
And I have several related tables in this style ...

我的问题是，这是最快、更好的方法吗?

My question is, is it the fastest, better way to do?

这不是直接放在USER表中，COUNTRY字段包含不同的ID(像这样:2, 6, ...")?

This would not be better to put directly in the USER table, COUNTRY field that contains the different ID (like this: "2, 6, ...")?

谢谢各位;)

推荐答案

就时间限制而言，您拥有它的方式是最佳的.当然，它会占用更多空间，但这是时空权衡 - 如果你想更快，你就使用更多的空间；如果你想使用更少的空间，它会运行得更慢(平均而言).

The way you have it is the most optimal as far as time constraints go. Sure, it takes up more space, but that's part of space-time tradeoff - If you want to be faster, you use more space; if you want to use less space, it will run slower (on average).

另外，想想未来.现在，您可能正在为每个用户选择国家/地区，但请稍等.由于范围蔓延的魔力，您的应用程序有一天需要选择给定国家/地区的所有用户，此时扫描每个用户的国家/地区"字段以查找匹配项将非常慢，而不是仅仅通过倒退USER_COUNTRY_REL 表就像你现在可以做的一样.

Also, think of the future. Right now, you're probably selecting the countries for each user, but just wait. Thanks to the magic of scope creep, your application will one day need to select all the users in a given country, at which point scanning each user's "COUNTRY" field to find matches will be incredibly slow, as opposed to just going backwards through the USER_COUNTRY_REL table like you could do now.

一般来说，对于 1 对 1 或 1 对多的关联，您可以通过外键进行链接.对于多对多相关性，您希望在两者之间有一个关系表.这个场景是多对多的关系，因为每个用户有多个国家，每个国家有多个用户.

In general, for a 1-to-1 or 1-to-many correlation, you can link by foreign key. For a many-to-many correlation, you want to have a relation table in between the two. This scenario is a many-to-many relationship, as each user has multiple countries, and each country has multiple users.

这篇关于关系表的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！