如何在MySQL中记录记录的顺序集合 [英] How to record sequential collections of records in MySQL

问题描述

假设我有一个表，其中包含某种类型的记录，比如说烹饪说明，如"Fold the melted chocolate into the egg whites".该表包含唯一的ID字段和字符串.

Suppose I have a table that holds some type of record, say cooking instructions like "Fold the melted chocolate into the egg whites". The table contains a unique ID field and the string.

我想为配方建立另一个表(每个表都有唯一的ID和名称)，每个表都是一系列顺序指令(某些指令将用于多个/许多配方).

I want to build another table for recipes (each with a unique ID and a name), each of which would be a series of sequential instructions (some instructions would be used for several/many recipes).

构造我的食谱表以将食谱的唯一ID映射到顺序系列指令(哪些ID不是顺序的)的最佳方法是什么?

What is the best way to structure my recipe table to map a recipe's unique ID to a sequential series of instructions (which IDs are not sequential)?

推荐答案

尝试这样的标准化设计:

Try a normalized design like this:

recipe
id  name
1   Recipe1
2   Recipe2

recipe_instruction
recipe_id  instruction_id  sortorder
1          5               1
1          3               2
1          4               3
2          6               1
2          7               2
2          3               3

要获取特定食谱的说明列表，可以使用以下查询:

To get the list of instructions for a specific recipe you can use this query:

SELECT i.the_string
FROM recipe_instruction AS ri
JOIN instruction AS i
ON ri.instruction_id = i.id
WHERE ri.recipe_id = 1
ORDER BY ri.sortorder

这篇关于如何在MySQL中记录记录的顺序集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！