如何为数据库添加多字段依赖？自定义下拉字段父子关系存储及优化方案咨询

Hey there, let's break down how to tackle these two edge cases—they're actually more common than you might think, and with a small tweak to your database design, you can handle both seamlessly.

Core Problem Recap #

You need:

1. A single child option (like "Texas") to be linked to multiple parent options (India and US)
2. Support for skip-level dependencies (e.g., Dubai → City, no State/Province in between)

Optimized Database Schema #

Instead of rigid parent-child tables tied to specific field levels, we'll use a flexible, option-to-option dependency model with three core tables:

1. custom_fields (Stores your dropdown definitions)

This table tracks all your custom select fields (e.g., "Country", "State/Province", "City"):

2. field_options (Stores all dropdown options across fields)

This holds every possible option for your selects—no hard ties to a specific parent level:

3. option_dependencies (The flexible glue for relationships)

This table explicitly maps parent options to child options—no restrictions on how many parents a child can have, or how many levels apart they are:

How This Solves Your Two Scenarios #

Scenario 1: Multi-Parent Child Options

Let's say "Texas" needs to be linked to both "India" and "US":

• First, ensure all three options exist in field_options:
  • "India" → field_id = Country field ID
  • "US" → field_id = Country field ID
  • "Texas" → field_id = State/Province field ID
• Add two separate rows in option_dependencies:
  1. parent_option_id = India's ID, child_option_id = Texas's ID
  2. parent_option_id = US's ID, child_option_id = Texas's ID

When your frontend loads the State/Province dropdown after a Country selection, it just queries option_dependencies where parent_option_id matches the selected Country ID—and gets all linked child options, including Texas for both parents.

Scenario 2: Skip-Level Dependencies (No Intermediate Level)

For Dubai (Country) → Dubai City (no State/Province):

• Add Dubai to field_options with field_id = Country field ID
• Add Dubai City to field_options with field_id = City field ID
• Add one row in option_dependencies:
  • parent_option_id = Dubai's ID, child_option_id = Dubai City's ID

When the user selects Dubai, your frontend can check if there are any child options linked to it. Since Dubai's children are directly City-level options, you can skip rendering the State/Province dropdown entirely and jump straight to the City select.

Extra Optimizations #

• Query Performance: Adding child_field_id to option_dependencies lets you quickly filter which field the child options belong to, avoiding extra joins to field_options when fetching dependencies.
• UI Handling: Use the is_leaf flag in field_options to disable further dropdowns once a leaf option is selected (e.g., if a City has no sub-regions).
• Extensibility: This schema supports unlimited hierarchy levels—if you later add a "District" field, you just link City options to District options in option_dependencies without changing table structures.
• Duplicate Prevention: The composite PK on option_dependencies ensures you don't accidentally create the same parent-child link multiple times.

内容的提问来源于stack exchange，提问作者Mustafa Dahodwala