当列数未知时逗号分隔值的替代解决方案 | MySQL
我需要设计一个几乎看起来像 3 维需求的表格。
例如，
- 恐龙有“无限”的品种。
- 所有恐龙都有一些共同的特征（名称、在地球上存在的年份、体重、姓氏等）。
- 有时会有一些与每个品种无关的特定特征。每个这样的功能都用“名称”=“值”对来标识。 “名称”类似于某个特征的名称（例如可能是头上的角、语音频率等），“值”可能是该特征的值（例如头上有 3 个角、某些 10hz 语音频率等） .,)。
根据要求，我们可以创建一个具有常见特征的表 X，例如引用 cid、名称、存在年份、重量等的列。可能还有另一个表 Y，只有列（uid、名称、值），其中 cid 可以绑定用于 uid。
但事实上，X 中的行数将是巨大的（万亿+，我们可能没有那么多恐龙家族，但这只是一个例子）。类似地，表 X 中的每个条目的名称=值将是数百个。这将增加表 Y 中的行数
。由于特征“名称”也是“无限”，因此我们无法创建具有固定列数的表不幸的是，就像每个功能的列一样，因为这就像动态列。
一种想法是在表 X 中再添加两列，其中一列可以包含以逗号分隔的所有特征（“名称”），另一列可以包含以逗号分隔的所有“值”。但我知道这是一个非常糟糕的设计。
对于这个需求，是否还有更好的解决方案呢？
I have a requirement to design a table which almost looks like a 3 dimensional requirement.
For example,
- There are "infinite" varieties of Dinosaurs.
- All the Dinosaurs will have some common set of features (name, years since existence on earth, weight, family name, etc.,).
- There will be some specific features which are sometimes unrelated to each variety. Each such feature is identified with a "name"="value" pair. The "name" is something like the name of a feature (like may be horns on head, the voice frequency etc) and the "value" could be the value of the feature (like 3 horns on the head, some 10hz voice frequency etc.,).
As per the requirement we can create a table X with common features like columns referring to cid, name, years since existence, weight etc.,. And there there could be another table Y with just columns (uid, name, value) where cid could be bound used for the uid.
But the fact that, the number of rows in X would be enormous (trillion+, we might not have so many dinosaur families, but that is just an example). Similarly, the name=value would be several hundreds per entry in table X. This would increase the number of rows in table Y.
Since, the feature "name" is also "infinite", we cannot create a table with fixed number of columns, like column per feature unfortunately because that would be like dynamic columns.
One idea would be to have two more columns in table X, where one column can consist of all the features ("name") as comma separated and the other column can consist of all the "values" in comma separated. But I know this is a very bad design.
Could there be any better solutions for this requirement?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看看下面的例子。您在
dinosaur_type中指定恐龙的种类，单个恐龙进入表dinosaur并链接到一个物种/类型，然后在dinosaur_feature中您可以为每个恐龙添加行，为恐龙添加任何类型（无限）可能的特征。Have a look at below example. You specify the species of dinosaurs in
dinosaur_type, the individual dinosaur go in tabledinosaurand are linked to a species/type and then indinosaur_featureyou can add rows for each individual dinosaur to add any kind of (infinite) possible feature for a dinosaur..