当前位置：文江博客话题详情

database-design classification

用于无限共享和独特项目分类的数据库设计模型

发布于 2024-12-28 12:35:01 字数 2019 浏览 5 评论 0 原文

本质上，我想创建一个数据库结构，允许以无限种方式对无限量的库存项目进行分类，但是，其中许多项目共享某些“特征”。以汽车和卡车为例：

两者都只能是红色或蓝色。
- 任一颜色的汽车/卡车都可以是 2wd 或 4wd。
  - 汽车可以配备手动或自动变速箱。
  - 卡车可以配备布料或皮革座椅
  - 等等......

我希望避免的事情是手动输入存在的每种可能的组合。有 5 种颜色和 5 种车辆，这已经是 25 个条目，并且没有功能集分类。

是否有一种数据模型可以允许这些关系和共享的“特征组”，或者更重要的是，是否有一个数据模型允许对我能想象到的任何数据集的每种可能组合进行单一引用？任何帮助将不胜感激。

已更新 [2012-01-23]

让我尽可能具体。我的主要目标是跟踪材料使用情况关于我们出于预算和历史目的所做的工作。一些材料，即螺柱和跑道，将共享相同的子分类，赛道还有第三个子分类。有些会完全不同的子分类。假设如下。

5 个可能的 metal_widths
5 个可能的 metal_gagues
4 个可能的 track_types
5 个可能的 insulation_widths
3 个可能的 insulation_types

...关系（可能的组合）：

螺柱 > metal_widths > > metal_gagues (25)
追踪 > metal_widths > > metal_gagues > > track_types (100)
绝缘 > 绝缘宽度> insulation_types (15)

只是为了了解我的最终目标，应用程序工作流程将是像这样的事情：

创造一份工作。
制定工作预算。
- 为我希望使用的每种材料设定预算金额/成本。
开始输入材料发票。
- 为发票上的每种材料设置金额/成本。
跟踪/审查我的预算估算与实际成本。

我认为我的预算与成本申请的目标非常简单，我只是想之前把材料相关的数据库设计正确继续前进。我意识到最简单的解决方案是创建一个在 material 表中输入每种可能的组合并限制数据库到 n 个可能的特征。问题是当我决定添加一个 x 宽度 螺柱，我还想添加一个 x 宽度 轨道，这意味着我已经可能的组合增加了 30 个，因此需要 30 个额外的条目（我真的宁愿避免）。

我的问题仍然相同：是否有一个数据模型允许这些关系，更重要的是，是否存在允许单一引用的关系每种可能性的分数...或者，我应该放弃这个概念并继续每种材料的单个条目并限制性状的数量。

原文

Essentially, I would like to create a database structure that allows for classifying an infinite amount of inventory items in an infinite amount of ways, however, many of these items share certain "traits". Take, for instance, Cars and Trucks:

Both can be red or blue only.
- Either color car/truck can be 2wd or 4wd.
  - Cars can have manual or automatic transmission.
  - Trucks can have cloth or leather seats
  - etc....

The thing I am looking to avoid would be manual entry of every possible combination that exists. With 5 colors and 5 vehicles, that's already 25 entries and no feature set classifications.

Is there a data model that allows for these relationships and shared "groups of traits", or more importantly, one that allows for a single reference to each possible combination of any set of data I can imagine? Any help would be greatly appreciated.

Updated [2012-01-23]

Let me be as specific as possible. My main goal is to track material usage
on jobs we are doing for budgetary and historical purposes. Some materials,
i.e. studs and track, will share the same sub-classifications, with
track having yet a 3rd sub-classification. Some will have completely
different sub-classifications. Assume the following.

5 possible metal_widths
5 possible metal_gagues
4 possible track_types
5 possible insulation_widths
3 possible insulation_types

...the relationships (possible combinations):

Studs > metal_widths > metal_gagues (25)
Track > metal_widths > metal_gagues > track_types (100)
Insulation > insulation_widths > insulation_types (15)

Just to get an idea of my ultimate goal, the application workflow would be
something like this:

Create a job.
Create a job budget.
- Set a budget amount/cost for each material I expect to use.
Begin entering material invoices.
- Set an amount/cost for each material on the invoice.
Track/Review my budget estimates vs. actual cost.

I think the goal of my budget vs cost application is pretty straight forward,
I just want to get the design of the material-related database correct before
moving forward. I realize the easiest solution would be to create a single
entry for each possible combination in a material table and limit that
database to n number of possible traits. The problem is that when I decide to
add an x width stud, I also want to add an x width track, meaning I've
increased the possible combinations by 30, and therefore require 30
additional entries (which I'd really rather avoid).

My question remains the same: Is there a data model that allows for these
relationships and, more importantly, is there one that allows single reference
points for each possibility... or, should I scratch this notion and go with
single entries for each material and limit the number of traits.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

归途 2025-01-04 12:35:01

你的意思是这样的吗？

在此处输入图像描述

这个简单的模型允许您将特征分组在一起，然后“应用”整个特征组到任意数量的项目（ITEM_TRAIT_GROUP 表是如何在关系范例中表示 M:N 关系的典型示例）。如果您主要关心的是通过“重用”特征来避免重复，则此模型可能适合您的要求。

然而，这将不会强制执行：

任何特定的特征组到特定的项目类型上（例如需要颜色的汽车），也不会强制执行特征之间的任何特定关系（例如座椅材料必须与座椅一起使用）颜色），
也不会限制任何特定特征的值（例如颜色必须是“红色”或“蓝色”，但不能是“绿色”）。

(1) 和 (2) 将需要某种类型系统，甚至需要“继承”（在 OOP 意义上），这在关系范式中并不有趣。如果您确实需要它，那么最好在客户端或中间层强制执行这种逻辑。

(3) 可以合理地进行关系建模，但模型并不复杂，这可能值得也可能不值得。

回复收藏 0 原文

半暖夏伤 2025-01-04 12:35:01

我不确定我是否理解“因此需要 30 个额外条目”的意思。您不必显式输入所有组合，除非它们实际上携带信息。例如，如果您有包含所有价格的供应商价目表，那么您将拥有 30 个额外行，每行都有一个新价格。但这似乎不是你想要的。您似乎想要输入组成工作的所有项目的预算价格和后来开具发票的价格。

让我们尝试以简单的方式解决这个问题：

METAL_WIDTH
id | unit | amount | displaytext
 1 |  mm  |   10   |  2/5 in
 2 |  mm  |   15   |  3/5 in
 3 |  mm  |   20   |  4/5 in
 4 |  mm  |   25   |  1 in
 5 |  mm  |   30   |  1 1/5 in

METAL_GAUGE...
TRACK_TYPE...
INSULATION_WIDTH...
INSULATION_TYPE...

我跳过了其他四个表的详细信息，它们的结构类似于 METAL_WIDTH

JOB
id | name
 1 | test job


BUDGET_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | price_in_dollar
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    50

INVOICE_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | price_in_dollar
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    49.95

这里我制作了 INVOICE_ITEM 和 BUDGET_ITEM 单独的表，因为我觉得您可能想要使用 INVOICE_ITEM 不仅仅是预算控制。但是您可以将所有项目放入一个大的 JOB_ITEMS 表中。相反的情况也是可能的：您可以创建一个 STUD_PRICE 表、一个 TRACK_PRICE 和一个 INSULATION_PRICE。更多表意味着查询变得更长，但现在您可以存储螺柱只能具有 METAL_GAUGE 和 METAL_WIDTH 属性的信息：

STUD_PRICE
id | job_id | purpose | metal_width_id | metal_gauge_id | price
 1 |    1   | BUDGET  |       1        |       1        |  50
 2 |    1   | INVOICE |       1        |       1        |  49.95

现在您可能会注意到存在冗余：相同的螺柱输入两次，如果出现这种情况，这将成为问题您想要比较预算和发票，并且在一项工作中有多个螺柱：

STUD_PRICE
id | job_id | purpose | metal_width_id | metal_gauge_id | price
 1 |    1   | BUDGET  |       1        |       1        |  50
 2 |    1   | INVOICE |       1        |       1        |  49.95
 3 |    1   | BUDGET  |       1        |       2        |  75
 4 |    1   | INVOICE |       1        |       2        |  89.95

现在哪个发票属于哪个预算？你只差一个错字就可以解决一个有趣的问题。因此，您可能最好这样做：

STUD_ITEM
id | job_id | metal_width_id | metal_gauge_id | budget_price | invoice_price
 1 |    1   |       1        |       1        |  50          |   49.95
 2 |    1   |       1        |       2        |  75          |   null

其中 null 表示“尚未开具发票”，一旦您到达那里，您可以从上面获取 BUGET_ITEM 和 INVOICE_ITEM 表并将它们组合到 JOB_ITEM

JOB_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | budget_price | invoice_price
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    50        | 49.95

您的应用程序将允许您创建一个新的作业，设置其属性，然后将项目添加到其预算中。您可以说“新项目...”，然后可以在螺柱、轨道和绝缘体之间进行选择。选择 STUD 后，您将获得一个包含允许的 METAL_WIDTH 的菜单，另一个包含允许的 METAL_GAUGE 的菜单。您选择它们，设置预算价格并存储该项目。根据需要重复。进入发票阶段后，您可以选择存储的项目并设置发票价格。预算/发票比较是通过检查作业中的所有项目、将所有预算价格添加为预计总预算、将所有发票添加为实际总发票金额来完成的，并且还可以仅显示那些项目的预算成本，从而获得奖励积分Invoice_price 不为空。

要为发票创建文本，您只需连接显示文本列的内容，从而无需为每个“Stud - 2/5 in - 10ga”到“Stud - 1 1/5 in - 18ga”设置一行。 ”。如果您现在添加 METAL_WIDTH，您所要做的就是向 METAL_WIDTH 表添加一行，就可以了。

I'm not sure i understand what you mean by "therefore require 30 additional entries". You don't have to enter all combinations explicitly unless they actually carry information. For instance, if you had a supplier's price list with all prices then you'd have 30 additional rows with a new price each. But that doesn't seem to be what you want. You seem to want an entry for the budgeted and later invoiced price of all the items that make up a job.

Let's try to tackle the problem in a straightforward way:

METAL_WIDTH
id | unit | amount | displaytext
 1 |  mm  |   10   |  2/5 in
 2 |  mm  |   15   |  3/5 in
 3 |  mm  |   20   |  4/5 in
 4 |  mm  |   25   |  1 in
 5 |  mm  |   30   |  1 1/5 in

METAL_GAUGE...
TRACK_TYPE...
INSULATION_WIDTH...
INSULATION_TYPE...

i skipped the details of the four other tables, their structure is analoguous to METAL_WIDTH

JOB
id | name
 1 | test job


BUDGET_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | price_in_dollar
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    50

INVOICE_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | price_in_dollar
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    49.95

Here i made INVOICE_ITEM and BUDGET_ITEM separate tables because i feel you'll probably want to use INVOICE_ITEM for more than just budget control. But you could throw all items into one big JOB_ITEMS table. The opposite is also possible: you could create a STUD_PRICE table, and a TRACK_PRICE, and a INSULATION_PRICE. More tables mean queries become longer, but now you can store the information that STUDs can only have METAL_GAUGE and METAL_WIDTH properties:

STUD_PRICE
id | job_id | purpose | metal_width_id | metal_gauge_id | price
 1 |    1   | BUDGET  |       1        |       1        |  50
 2 |    1   | INVOICE |       1        |       1        |  49.95

And now you'll probably notice that you have a redundancy: the same stud is entered twice, which will become a problem if you'd like to compare budget and invoice, and have more than one stud in a job:

STUD_PRICE
id | job_id | purpose | metal_width_id | metal_gauge_id | price
 1 |    1   | BUDGET  |       1        |       1        |  50
 2 |    1   | INVOICE |       1        |       1        |  49.95
 3 |    1   | BUDGET  |       1        |       2        |  75
 4 |    1   | INVOICE |       1        |       2        |  89.95

which INVOICE belongs to which BUDGET now? You're one typo away from an interesting problem. So you're probably better off with:

STUD_ITEM
id | job_id | metal_width_id | metal_gauge_id | budget_price | invoice_price
 1 |    1   |       1        |       1        |  50          |   49.95
 2 |    1   |       1        |       2        |  75          |   null

where null means "not invoiced yet", and once you're there, you can take the BUGET_ITEM and INVOICE_ITEM tables from above and combine them to a JOB_ITEM

JOB_ITEM
id | job_id | type | metal_width_id | metal_gauge_id | track_type_id | insulation_width_id | insulation_type_id | budget_price | invoice_price
1  |    1   | STUD |       1        |      1         |    null       |      null           |     null           |    50        | 49.95

Your application would allow you to create a new job, set its attributes, and then add items to its budget. You could say "New item..." and have the choice between STUDs, TRACKs and INSULATIONs. Once you choose STUD, you get a menu with the allowed METAL_WIDTHs, another with the allowed METAL_GAUGEs. You choose them, set a budget price and store the item. Repeat as necessary. Once you get to the invoice stage, you select the stored item and set the invoice price. Budget/invoice comparison is done by going over all items in a job, adding all budget prices for a projected total budget, and adding all invoices for the actual total invoiced amount, with bonus points for also showing the budgeted cost for only those items where invoice_price is not null.

To create text for the invoice, you just have to concatenate the contents of the displaytext columns, thus saving the need for a row for each of "Stud - 2/5 in - 10ga" through "Stud - 1 1/5 in - 18ga". If you now add a METAL_WIDTH, all you have to do is add one row to the METAL_WIDTH table, and you're good.

回复收藏 0 原文

一场信仰旅途 2025-01-04 12:35:01

开设汽车课程。汽车可以是卡车或汽车。也许您应该研究继承和抽象——面向对象设计的两个基础。

回复收藏 0 原文

荒人说梦 2025-01-04 12:35:01

我不确定“每种可能性的单一参考点”是什么意思。

您只需将数据输入宽的 varchar() 列，如

Stud, 8 mm, 8 ga
Stud, 10 mm, 8 ga
Stud, 12 mm, 6 ga
Track, 8mm, 8 ga类型 1
轨道，10 毫米，8 GA 类型 2

这不需要您提前输入任何内容，并且它提供了最大的灵活性，尽管这种灵活性是以数据为代价的正直。（或者因不断观察条目中的错误而增加管理开销。）它还会使报告复杂化。例如，仅报告 8 个规格螺柱就更复杂。

现成的会计系统——已经被编程来处理预算、估算和发票——通常会要求您以这种方式存储库存。

假设您已经做出了有意识、明智的决定，不使用会计系统来实现此目的，并且您根本不会以这种方式存储值，而是希望通过外键约束来获得更高的数据完整性，那么它可能会自动生成集合是有意义的。

-- This table will be used as a foreign key reference for tracks. 
-- Adjust the CHECK() constraints for your actual values. If you're using MySQL,
-- replace the CHECK() constraints with foreign key references to separate tables.
--
create table studs (
  metal_width_mm integer not null check (metal_width_mm between 5 and 10),
  metal_gauge integer not null check (metal_gauge between 8 and 16),
  primary key (metal_width, metal_gauge)
);

要填充它，请将已知值放入公共表表达式中，并生成笛卡尔积。

insert into studs
with gauge as (
  select 8 as metal_gauge
  union all
  select 10
  union all
  select 12
  union all
  select 14
  union all
  select 16
),
width as (
  select 5 as metal_width_mm
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9
  union all
  select 10
)
select * 
from width, gauge

如果您必须经常添加值，那么编写 SQL 存储过程来仅插入新组合并不困难。

如果您不寻求如此大的灵活性或如此多的数据完整性，请考虑澄清您的问题。

I'm not sure what you mean by "single reference points for each possibility".

You could simply enter your data into a wide, varchar() column, as

Stud, 8 mm, 8 ga
Stud, 10 mm, 8 ga
Stud, 12 mm, 6 ga
Track, 8mm, 8 ga type 1
Track, 10 mm, 8 ga type 2

This doesn't require you to enter anything ahead of time, and it offers the greatest flexibility, although that flexibility comes at the cost of data integrity. (Or increased administrative overhead from constantly watching the entries for mistakes.) It can also complicate reports. For example, reporting only 8 gauge studs is more complex.

Off-the-shelf accounting systems--which are already programmed to deal with budgets, estimates, and invoices--will usually require you to store inventory in just this way.

Assuming that you've already made the conscious, informed decision not to use your accounting system for this, that you're simply not going to store values this way, and instead want the greater data integrity that comes with foreign key constraints, it might make sense to generate the sets automatically.

-- This table will be used as a foreign key reference for tracks. 
-- Adjust the CHECK() constraints for your actual values. If you're using MySQL,
-- replace the CHECK() constraints with foreign key references to separate tables.
--
create table studs (
  metal_width_mm integer not null check (metal_width_mm between 5 and 10),
  metal_gauge integer not null check (metal_gauge between 8 and 16),
  primary key (metal_width, metal_gauge)
);

To populate it, put the known values into common table expressions, and generate a Cartesian product.

insert into studs
with gauge as (
  select 8 as metal_gauge
  union all
  select 10
  union all
  select 12
  union all
  select 14
  union all
  select 16
),
width as (
  select 5 as metal_width_mm
  union all
  select 6
  union all
  select 7
  union all
  select 8
  union all
  select 9
  union all
  select 10
)
select * 
from width, gauge

If you have to add values often, it's not hard to write a SQL stored procedure to insert only the new combinations.

If you're not looking for either this much flexibility or this much data integrity, consider clarifying your question.

回复收藏 0 原文

十秒萌定你 2025-01-04 12:35:01

当我在奥南工作时，他们必须跟踪发电机组的有效配置，他们使用两个表，“必须使用”和“不允许”。

因此，给出您的示例：

cars and trucks,
    Both can be red or blue only.
    Either color car/truck can be 2wd or 4wd.
        Cars can have manual or automatic transmission.
        Trucks can have cloth or leather seats

在“必须使用”表中，会有类似

object | 的规则。属性|必须使用1 | orMustUse2

car | color | red | blue
truck | color | red | blue
car | wd | 2wd | 4wd
truck | wd | 2wd | 4wd
car | transmission | manual | automatic
truck | seats | cloth | leather

在您的情况下，如果您有很多重复项，您可能可以存储更高级别的关系，例如 cars 和 trucks 是autos，然后在“必须使用”表中，只需有

auto | color | red | blue
auto | wd | 2wd | 4wd
car | transmission | manual | automatic
truck | seats | cloth | leather

另外，我不确定为什么可能的组合会让您困惑。
给定

    5 possible metal_widths
    5 possible metal_gagues
    4 possible track_types
    5 possible insulation_widths
    3 possible insulation_types

...the relationships (possible combinations):

    Studs > metal_widths > metal_gagues (25)
    Track > metal_widths > metal_gagues > track_types (100)
    Insulation > insulation_widths > insulation_types (15)

一个表格，其中包含 5 行的 metal_widths、5 行的 metal_gauges、带有 metal_widthID 和 metal_gaugeID 的 studs、track 带有（studID？）和 track_typeID，绝缘 带有insulation_widthID 和insulation_typeID。

如果您觉得自己的经验不足以建模然后应用数据，您可以让其他人来做。

When I worked at Onan and they had to track valid configurations of generator sets, they used two tables, 'must use' and 'not allowed.'

So, given your example:

cars and trucks,
    Both can be red or blue only.
    Either color car/truck can be 2wd or 4wd.
        Cars can have manual or automatic transmission.
        Trucks can have cloth or leather seats

in the 'must use' table, there would be rules like

object | attribute | mustUse1 | orMustUse2

car | color | red | blue
truck | color | red | blue
car | wd | 2wd | 4wd
truck | wd | 2wd | 4wd
car | transmission | manual | automatic
truck | seats | cloth | leather

In your case, if you have a lot of duplicates, you could probably store a higher-level relationship, like cars and trucks are autos, and then in the 'must use' table, just have

auto | color | red | blue
auto | wd | 2wd | 4wd
car | transmission | manual | automatic
truck | seats | cloth | leather

Also, I'm not sure why the possible combinations are hanging you up.
Given

    5 possible metal_widths
    5 possible metal_gagues
    4 possible track_types
    5 possible insulation_widths
    3 possible insulation_types

...the relationships (possible combinations):

    Studs > metal_widths > metal_gagues (25)
    Track > metal_widths > metal_gagues > track_types (100)
    Insulation > insulation_widths > insulation_types (15)

Have a table for metal_widths with 5 rows, metal_gauges with 5 rows, studs with a metal_widthID and metal_gaugeID, track with a (studID?) and track_typeID, and insulation with insulation_widthID and insulation_typeID.

If you don't feel like your experience is adequate to model and then apply the data, you could have someone else do it.

回复收藏 0 原文

过气美图社 2025-01-04 12:35:01

RDBMS 是必需的吗？您可以使用像 graphDB 吗="nofollow">Neo4j 代替？

回复收藏 0 原文

闻呓 2025-01-04 12:35:01

我会通过将项目（和引号）建模为具有类型的对象，并使用键值对指定特征来实现它。下面通过示例显示表格，并忽略主键之类的内容：

首先有一个包含所有有效键值对的表格：

VALID_KEY_VALUES
| key             | value   |
=============================
| metal_width     | mwidth1 |
| metal_width     | mwidth2 |
...
| insulation_type | itype3  |

这满足了您的要求，如果您需要添加新的 metal_width，那么它就是一个插入到该表中。（当前示例中有 22 行）。

现在有一个包含每种类型的有效键的表：

VALID_TYPE_KEYS
| type       | key              |
=================================
| stud       | metal_width      |
| stud       | metal_gauge      |
| track      | metal_width      |
| track      | metal_gauge      |
| track      | track_type       |
| insulation | insulation_width |
| insulation | insulation_type  |

现在一个项目（或引用）由两个表定义，首先是按 ID 定义的项目：

ITEMS
| ID | type | .... whatever columns you need ...
================================================
| 1  | stud | ..................................

现在该项目的“特征”：（

ITEM_TRAITS
| itemID | type | key         | value   |
=========================================
| 1      | stud | metal_width | mwidth1 |
| 1      | stud | metal_gauge | mgauge2 |

这有点非规范化，因为每行包含来自 ITEMS 表的 itemID 和 type，但我们暂时可以接受。换句话说，第 1 项是具有 metal_width=mwidth1, metal_gauge=mgauge2 的螺柱。

您可以使用 ITEM_TRAITS 表中的多列外键将它们粘在一起。

(itemID, type) is a foreign key into the ITEMS table
(type, key) is a foreign key to the VALID_TYPE_KEYS table
(key, value) is a foreign key to the VALID_KEY_VALUES table

这看起来是您需要的一个良好的开始。如果您需要添加新类型、新特征或特征的新值，则可以通过最少的插入完成。您可以使用 QUOTES 和 QUOTE_TRAITS 表进行类似的报价。您可以通过连接将项目与引号进行匹配，计算项目和引号之间匹配的特征数量（并将其与给定类型的特征总数进行比较）。

您可能需要在客户端上做一些工作，迭代给定类型的所有键，以确保输入所有必要的数据，并且这确实假设您的每一个“组合”都是有效的。（如果您确实遇到某些组合有效而某些组合无效的情况，那么您将无法避免将它们全部枚举出来）。这还假设也没有“向下钻取”逻辑，即指定一个特征不会限制任何后续特征的值，但您甚至可以将这个想法扩展到那种情况（前提是您的要求不是太疯狂了）。

I'd approach it by modeling items (and quotes) as objects with a type, and specifying traits using key-value pairs. Showing tables in what follows by example, and ignoring things like primary keys:

First have a table of all valid key-value pairs:

VALID_KEY_VALUES
| key             | value   |
=============================
| metal_width     | mwidth1 |
| metal_width     | mwidth2 |
...
| insulation_type | itype3  |

This addresses your requirement, if you need to add a new metal_width, then it's one insert into this table. (Currently 22 rows in your example).

Now have a table of valid keys for each type:

VALID_TYPE_KEYS
| type       | key              |
=================================
| stud       | metal_width      |
| stud       | metal_gauge      |
| track      | metal_width      |
| track      | metal_gauge      |
| track      | track_type       |
| insulation | insulation_width |
| insulation | insulation_type  |

Now an item (or a quote) is defined by two tables, first, the items by ID:

ITEMS
| ID | type | .... whatever columns you need ...
================================================
| 1  | stud | ..................................

And now the "traits" of that item:

ITEM_TRAITS
| itemID | type | key         | value   |
=========================================
| 1      | stud | metal_width | mwidth1 |
| 1      | stud | metal_gauge | mgauge2 |

(This is a bit denormalized, because each row contains the itemID and the type from the ITEMS table, but we can live with that for the moment). In other words, item 1 is a stud with metal_width=mwidth1, metal_gauge=mgauge2.

You can stick it all together by using multiple-column foreign keys from the ITEM_TRAITS table.

(itemID, type) is a foreign key into the ITEMS table
(type, key) is a foreign key to the VALID_TYPE_KEYS table
(key, value) is a foreign key to the VALID_KEY_VALUES table

This looks like a good start on what you need. If you need to add a new type, a new trait, or a new value of the trait, it's done with the minimum inserts. Your quotes can be done similarly, with QUOTES and QUOTE_TRAITS tables. You can match items with quotes by joins, counting how many traits match between item and quote (and comparing that with the total number of traits for a given type).

You may need to do some work on the client, iterating over all keys for a given type, to make sure all the necessary data gets entered, and this does assume that every one of your "combinations" is valid. (If you do have a situation where some combinations are valid and some are not, then you won't be able to avoid enumerating them all). This also assumes there's no "drilldown" logic either, that specifying one trait doesn't restrict the values of any later ones, but you may even be able to extend this idea to that case as well (provided your requirements aren't too insane).