将 id 存储在 MySQL-Link-Table 中
我有一个表“link_tabl”，我想在其中通过 id 链接其他三个表。所以每一行都有一个三元组（id_1，id_2，id_3）。我可以为三元组的每个元素创建一个列，一切都会好起来的。
但我想要更多：=）
我需要尊重另一个“维度”。有一个算法可以创建三元组（表之间的链接）。该算法有时会输出不同的链接。
示例：
table_person 代表一个人。 table_task代表一个任务。 table_loc 重新表示一个位置。
所以 ids (p, t, l) 的三元组意味着：某个人在某个位置做了某事。
元组（人员、任务）不会被算法更改。他们是被给予的。该算法为元组 (p,t) 输出位置 l。但有时算法会为这样的元组确定不同的位置。我想将每个元组（作者、任务）的最后 10 个三元组存储在表中。
最好的方法是什么？
我想到了类似的事情： 如果 link_table 中已存储元组 (p,t)，则将位置 id 添加到该行的下一个空闲槽（列）中。 如果已经有 10 个值（所有列都已满），请删除第一个值，将每个值从第 i 列移动到第 i-1 列，并将新值存储在最后一列中。 否则添加一个新行。
但我不知道这是否是一个好的方法，如果是，如何实现......
自己的部分解决方案 我发现我可以制作两列。 Onw 存储作者 id。存储任务 ID 的一个。这样
...
UNIQUE INDEX (auth_id, task_id)
...
我就可以索引它们了。所以现在我只需要弄清楚如何优雅地将值从第 i 列移动到 i-1 。 =)
亲切的问候 奥夫风
I have a table "link_tabl" in which I want to link three other tables by id. So in every row I have a triplets (id_1, id_2, id_3). I could create for every element of the triplet a column and everything would be fine.
But I want more: =)
I need to respect one more "dimension". There is an Algorthm who creates the triplets (the linkings between the tables). The algorithm sometimes outputs different linkings.
Example:
table_person represents a person.
table_task represents a task.
table_loc reüpresents a location.
So a triplet of ids (p, t, l) means: A certain person did something at some location.
The tuple (person, task) are not changed by the algorithm. They are given. The algorithm outputs for a tuple (p,t) a location l. But sometimes the algorithm determines different locations for such a tuple. I want to store in a table the last 10 triplets for every tuple (author, task).
What would be the best approach for that?
I thought of something like:
IF there is a tuple (p,t) ALREADY stored in link_table ADD the id of location into the next free slot (column) of the row.
If there are already 10 values (all columns are full) delete the first one, move every value from column i to column i-1 and store the new value in the last column.
ELSE add a new row.
But I don't know if this is a good approach and if it is, how to realise that...
Own partial solution
I figured out, that I could make two columns. Onw which stores the author id. One which stores the task id. And by
...
UNIQUE INDEX (auth_id, task_id)
...
I could index them. So now I just have to figure out how to move values from column i to i-1 elegantly. =)
Kind regards
Aufwind
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我将算法的输出存储在行中，并带有日期指示器。仅考虑最后 10 条记录的要求听起来相当随意 - 我不会将其纳入我的列布局中。它还使一些标准关系工具变得多余 - 例如，查询“人员 x 和位置 y 存在多少个位置”无法通过“计数”来回答，而是通过查看哪一列为空来回答。
因此，我建议这样：
可以通过在选择查询中使用“top 10”来强制执行“only 10”要求；如果需要，您甚至可以将其嵌入到视图中。
I would store the output of the algorithm in rows, with a date indicator. The requirement to only consider the last 10 records sounds fairly arbitrary - and I wouldn't enshrine it in my column layout. It also makes some standard relational tools redundant - for instance, the query "how many locations exist for person x and location y" couldn't be answered by "count", but instead by looking at which column is null.
So, I'd recommend something like:
The "only 10" requirement could be enforced by using "top 10" in select queries; you could even embed that in a view if necessary.