Great question! Let’s break this down step by step—you’re aiming for the sweet spot between a rock-solid relational foundation for your core Article data and flexible, dynamic extensions to handle special categories and time-limited business rules, which makes the TOXI pattern paired with JSON a perfect fit. Let’s map this to your tech stack (C# Nancy backend, React-Redux-GraphQL frontend) and your specific requirements.

First, lock down your immutable Article data in a traditional relational table. This ensures ACID compliance, fast querying, and data integrity for fields you know won’t change frequently:

CREATE TABLE Article (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Code NVARCHAR(50) UNIQUE NOT NULL, -- Unique article identifier
    Description NVARCHAR(255) NOT NULL,
    Qty INT NOT NULL DEFAULT 0,
    Value DECIMAL(18,2) NOT NULL, -- Base cost value
    Price DECIMAL(18,2) NOT NULL, -- Base selling price
    CategoryId INT NOT NULL FOREIGN KEY REFERENCES Category(Id), -- Fixed core category
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE Category (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(255)
);

This table handles all your "core stable" data—no JSON here, just structured, query-friendly fields.

The TOXI pattern (Tuple-Object-JSON) shines here: we’ll use relational tables for context and JSON for unstructured/dynamic data that needs to adapt to new business rules.

2.1 Handle Special Categories #

Special categories are likely ad-hoc, multi-dimensional, or short-lived—perfect for JSON. You have two options depending on query needs:

• Simple JSON Field (for low-query use cases): Add a JSON array directly to the Article table to store tags like ["Holiday2024", "VIPExclusive"]:

  ALTER TABLE Article ADD SpecialCategories NVARCHAR(MAX) NULL;
  

• Relational + JSON (for query-heavy use cases): If you need to filter or aggregate by special categories, use a join table with optional JSON metadata:

  CREATE TABLE ArticleSpecialCategory (
      ArticleId INT FOREIGN KEY REFERENCES Article(Id),
      CategorySlug NVARCHAR(100) NOT NULL, -- e.g., "Clearance"
      Metadata NVARCHAR(MAX) NULL, -- JSON for extra context like {"priority": 1, "badgeColor": "#FF5733"}
      PRIMARY KEY (ArticleId, CategorySlug)
  );
  

2.2 Manage Time-Limited Specializations #

For features like temporary discounts, limited-stock rules, or custom attributes that expire, create a dedicated table to track validity windows and store dynamic rules in JSON:

CREATE TABLE ArticleSpecialization (
    Id INT PRIMARY KEY IDENTITY(1,1),
    ArticleId INT FOREIGN KEY REFERENCES Article(Id) ON DELETE CASCADE,
    EffectiveStartDate DATETIME2 NOT NULL,
    EffectiveEndDate DATETIME2 NOT NULL,
    SpecialType NVARCHAR(50) NOT NULL, -- e.g., "Discount", "LimitedStock"
    SpecialData NVARCHAR(MAX) NOT NULL, -- JSON with type-specific rules
    IsActive AS CASE WHEN GETUTCDATE() BETWEEN EffectiveStartDate AND EffectiveEndDate THEN 1 ELSE 0 END PERSISTED
);

Example JSON payloads for different specializations:

• Discount: {"discountPercentage": 20, "minPurchaseQty": 2}
• Limited Stock: {"remainingStock": 50, "alertThreshold": 10}
• Custom Attribute: {"customLabel": "Summer Exclusive", "displayBadge": true}

Nancy makes handling JSON straightforward—use System.Text.Json or Newtonsoft.Json to serialize/deserialize dynamic fields:

// Core Article model with dynamic JSON support
public class Article
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Description { get; set; }
    public int Qty { get; set; }
    public decimal Value { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public JsonElement? SpecialCategories { get; set; } // Dynamic JSON array
    public List<ArticleSpecialization> Specializations { get; set; } = new();
}

// Specialization model
public class ArticleSpecialization
{
    public int Id { get; set; }
    public int ArticleId { get; set; }
    public DateTime EffectiveStartDate { get; set; }
    public DateTime EffectiveEndDate { get; set; }
    public string SpecialType { get; set; }
    public JsonElement SpecialData { get; set; }
    public bool IsActive { get; set; }
}

// Nancy module example
public class ArticlesModule : NancyModule
{
    private readonly IArticleRepository _repo;

    public ArticlesModule(IArticleRepository repo) : base("/api/articles")
    {
        _repo = repo;

        Get("/{id:int}", async (args) =>
        {
            var article = await _repo.GetByIdWithSpecializations(args.id);
            return Response.AsJson(article);
        });

        Post("/", async (args) =>
        {
            var article = this.Bind<Article>();
            // Validate JSON data here if needed (e.g., check SpecialData matches SpecialType)
            await _repo.Create(article);
            return Response.AsJson(article, HttpStatusCode.Created);
        });
    }
}

Pro tip: Add JSON schema validation in the repository layer to ensure dynamic data adheres to business rules for each SpecialType.

GraphQL is ideal for mixed structured/dynamic data—use a JSON scalar type to handle flexible fields, and let Redux manage state for dynamic UI rendering:

GraphQL Schema Example #

scalar JSON

type Category {
  id: ID!
  name: String!
  description: String
}

type ArticleSpecialization {
  id: ID!
  effectiveStartDate: String!
  effectiveEndDate: String!
  specialType: String!
  specialData: JSON!
  isActive: Boolean!
}

type Article {
  id: ID!
  code: String!
  description: String!
  qty: Int!
  value: Float!
  price: Float!
  category: Category!
  specialCategories: JSON
  specializations: [ArticleSpecialization!]!
}

type Query {
  getArticle(id: ID!): Article
}

React Component Example #

import { useQuery } from '@apollo/client';
import { GET_ARTICLE } from '../graphql/queries';

function ArticleDetail({ articleId }) {
  const { data, loading } = useQuery(GET_ARTICLE, { variables: { articleId } });

  if (loading) return <div>Loading article details...</div>;
  const { article } = data;

  return (
    <div className="article-card">
      <h2>{article.description}</h2>
      <p className="price">${article.price.toFixed(2)}</p>
      
      {/* Render special categories */}
      {article.specialCategories && (
        <div className="special-categories">
          <h3>Tags:</h3>
          {article.specialCategories.map(tag => (
            <span key={tag} className="category-tag">{tag}</span>
          ))}
        </div>
      )}

      {/* Render active time-limited specializations */}
      {article.specializations.filter(spec => spec.isActive).map(spec => (
        <div key={spec.id} className={`specialization ${spec.specialType}`}>
          <h4>{spec.specialType}</h4>
          {spec.specialType === 'Discount' && (
            <p>Save {spec.specialData.discountPercentage}% until {new Date(spec.effectiveEndDate).toLocaleDateString()}</p>
          )}
          {spec.specialType === 'LimitedStock' && (
            <p>Only {spec.specialData.remainingStock} left in stock!</p>
          )}
        </div>
      ))}
    </div>
  );
}

In Redux, store the full Article object including JSON fields—use Immer to simplify immutable updates for dynamic data.

• Index JSON Fields: If you query JSON data frequently, use database-specific JSON indexes (e.g., SQL Server’s JSON indexes, PostgreSQL’s GIN indexes) to boost performance.
• Version Dynamic Data: Add a SchemaVersion field to JSON payloads to handle breaking changes in business rules without breaking frontend code.
• Keep Core Data Clean: Never put stable fields in JSON—reserve JSON for truly dynamic, business-rule-dependent data only.

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