如何构建将被输入下拉框的数据？

I have denormalized data:

我有非规范化数据:

+----+----------+------+--------+
| pk |   name   | type | animal |
+----+----------+------+--------+
|  1 | alex     | car  | cat    |
|  2 | alex     | bike | cat    |
|  3 | liza     | car  | dog    |
|  4 | danielle | bike | dog    |
|  5 | danielle | bus  | dog    |
+----+----------+------+--------+

I would like to have 3 dropdown boxes.

我想有3个下拉框。

name
type
animal

after the user chooses the option for the first, there should be a cascade effect for the other dropdowns.

在用户选择第一个选项后,应该有其他下拉列表的级联效果。

example: if the user chooses danielle for the name, the only two options for type would be bike and bus and the only option for animal would be dog

例如:如果用户选择danielle作为名称,则类型的唯一两个选项是自行车和公共汽车,动物的唯一选择是狗

How should I structure the SQL tables? Should I denormalize?

我应该如何构建SQL表?我应该反规范吗?

1 个解决方案

#1

I'd say that your solution depends on how much data do you have in this table. If this table is relatively small, you could load it into memory, fill comboboxes by distinct values and then filter data by chosen field.

我会说你的解决方案取决于你在这张表中有多少数据。如果此表相对较小,您可以将其加载到内存中,通过不同的值填充组合框,然后按选定的字段过滤数据。

If it large, you maybe should denormalize your table as @astander says, fill comboboxes with data from reference tables and then when value changes, select filters from SQL like:

如果它很大,你可能应该像@astander所说的那样对表进行非规范化,用参考表中的数据填充组合框,然后当值改变时,从SQL中选择过滤器,如:

declare @name_id int -- input parameter, fill it with id of chosen name

-- filter for type combo
select distinct type_id from main_table where name_id = @name_id

-- filter for animal combo
select distinct animal_id from main_table where name_id = @name_id

1 个解决方案

#1

更多相关文章

随机推荐