Laravel 5.6：如何为垂直表创建模型工厂？

Hey there, great call going with a vertical (EAV-style) table for your Fields instead of a wide horizontal one—this is exactly the right move when dealing with dynamic, variable numbers of fields across Leads, especially since some have way more than others. Let’s break down some practical implementation details and optimizations for your setup:

1. Why This Design Makes Sense for Your Use Case #

• Avoids MySQL's Row size too large error: Horizontal tables hit InnoDB's default row size limit (~65KB) quickly when supporting unlimited whitelisted fields. A vertical structure eliminates this risk entirely.
• Efficient storage: For Leads with only 5 fields, you don’t waste space on dozens of empty columns that would come with a horizontal table.

2. Model Factory Examples (Laravel-Based, Adjust to Your Framework) #

Since you already have a Lead model factory, here’s how to extend it to generate associated Fields, plus a dedicated Field factory to handle dynamic, whitelisted data:

Lead Model Factory #

use App\Models\Lead;
use Illuminate\Database\Eloquent\Factories\Factory;

class LeadFactory extends Factory
{
    protected $model = Lead::class;

    public function definition()
    {
        return [
            'name' => $this->faker->company,
            'primary_email' => $this->faker->unique()->companyEmail,
            // Add other core Lead fields here
        ];
    }

    // Attach random number of Fields to a Lead (matches your variable count scenario)
    public function withFields($count = null)
    {
        $fieldCount = $count ?? $this->faker->numberBetween(5, 60);
        return $this->has(\App\Models\Field::factory()->count($fieldCount), 'fields');
    }
}

Field Model Factory #

use App\Models\Field;
use Illuminate\Database\Eloquent\Factories\Factory;

class FieldFactory extends Factory
{
    protected $model = Field::class;

    public function definition()
    {
        // Replace with your actual whitelisted field keys
        $whitelistedKeys = [
            'company_size', 'annual_revenue', 'industry', 'contact_phone', 
            'billing_address', 'lead_source', 'decision_maker_name'
        ];

        $selectedKey = $this->faker->randomElement($whitelistedKeys);

        return [
            'key' => $selectedKey,
            // Generate value matching the field type for realism
            'value' => match ($selectedKey) {
                'company_size' => $this->faker->numberBetween(10, 1500),
                'annual_revenue' => '$' . number_format($this->faker->randomFloat(2, 50000, 20000000)),
                'industry' => $this->faker->randomElement(['Tech', 'Finance', 'Healthcare', 'Retail']),
                default => $this->faker->words(3, true),
            },
            // Optional: Track field data type for frontend rendering
            'data_type' => $this->faker->randomElement(['string', 'integer', 'currency', 'text'])
        ];
    }
}

3. Optimization Tips for Your Vertical Table #

• Add a composite index on fields(lead_id, key): This drastically speeds up queries like "get all fields for Lead X" or "find the 'annual_revenue' value for Lead Y". Run this command:

  CREATE INDEX idx_lead_key ON fields(lead_id, key);
  

• Redundancy for high-frequency fields: If certain fields (like primary_email or decision_maker_name) are queried constantly, consider duplicating them in the leads table to avoid joining with fields every time.
• Enforce whitelist integrity: If your whitelisted fields are static, add an ENUM constraint to the key column, or create a field_definitions lookup table to store valid keys and reference it via a foreign key in fields.

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