时间戳

首页 > 解决方案 > Set-up a Child Table that always has the same number of rows as the Parent table?

html - Set-up a Child Table that always has the same number of rows as the Parent table?

问题描述

I was wondering if there is any way to set-up the relationship between a Child and Parent table where the Child Table always has the same number of rows as the Parent Table.

An Example : The Parent table acts as a 'Master-list' or 'Dictionary' for ingredients used in a kitchen, within the table it would have multiple columns that store information on the ingredient such as - a unique ID, ingredient name, a supplier/brand, a unit of measurement, etc.

The Child table represents different locations where ingredients are stored, (Different Branches) - The Child Table would probably only have 2 columns being the ingredient's unique ID and Quantity. The child table needs to contain the same number of rows as Parent table. If a new entry is created in the Parent Table then the Child table should also have the new entry with Quantity = 0.

Alternative Solution : In the case that this isn't possible, What I currently have in mind in order to achieve this would be how I handle the forms, If a new entry into the Parent table is made it would also have code to insert the new entry into the existing Child tables, Though this would mean every-time there is a new location (new child table) I could also create a sort of form that checks if the Parent table and a specific Child table has the same number of Rows.

Thank you for your time and input.

标签： htmlmysql

解决方案

您计划为将存储成分数量的每个位置创建一个子表。这将产生多个表与成分表具有 1-1 关系的情况。此规范化不适用于您的用例。

考虑以下替代设计：

  • 表ingredients存储成分主数据
  • 表locations列出了不同的位置
  • 表ingredients_locations存储每个位置的每种产品的数量：它有一个引用成分 id 的外键，还有一个指向位置 id 的外键

推荐阅读