数据库设计 - 存储条件规则？ [关闭]

5 回答

• 2

  你有什么内置的，但MSDN上有一篇文章描述了你如何构建自己的文章 . 对于一条规则来说，它似乎有点过度设计，但也许您的应用程序也有其他规则 . Find out more.

  回复于 2024-05-03T22:15:05+08:00
• 0

  您始终可以将此业务逻辑放在负责执行提款的存储过程中 . 这样，当您想要更改逻辑时，您不必重新部署Web应用程序 .

  回复于 2024-05-03T22:15:05+08:00
• 0

  我的方法，最简单的形式，如下所示 .

  USE tempdb
  GO
  IF OBJECT_ID('tempdb.dbo.Transactions') IS NOT NULL DROP TABLE Transactions
  
  SELECT TOP 500 TxnVal = ABS(CHECKSUM(NEWID())) % 1000
  INTO Transactions
  FROM sys.columns
  
  ;WITH Fee (Id, VariableAmountFloor, VariableAmountCeiling, VariableAmount, FixedAmount, CurrencyCode) AS
  (
      SELECT 1, 0,    200,        0.8,    0.1, 'x'    UNION ALL
      SELECT 2, 201,  300,        0.65,   0.1, 'x'    UNION ALL
      --  NB: have chosen 2147483647 as the ceiling because it is the maximum value of the INT type
      --  You will probably have some sort of float
      SELECT 3, 301,  2147483647, 0.4,    0.1, 'x'
  )
  SELECT   T.TxnVal
          ,VariableFee    = F.VariableAmount
          ,FixedFee       = F.FixedAmount
          ,TxnPlusCosts   = T.TxnVal + F.VariableAmount + F.FixedAmount
  FROM Transactions   T
  JOIN Fee            F ON T.TxnVal BETWEEN F.VariableAmountFloor AND F.VariableAmountCeiling
  

  回复于 2024-05-03T22:15:05+08:00
• 2

  SET ANSI_WARNINGS ON;
  GO
  
  --DROP TABLE dbo.FeeInterval
  CREATE TABLE dbo.FeeInterval (
      FeeIntervalID INT IDENTITY(1,1),    
          CONSTRAINT PK_FeeInterval PRIMARY KEY (FeeIntervalID),
  
      StartValue NUMERIC(18,2) NOT NULL,
          CONSTRAINT CK_FeeInterval_StartValue CHECK (StartValue > 0),
      -- If EndValue IS NOT NULL Then the interval is TxValue BETWEEN [StartValue, EndValue]
      -- Else (EndValue IS NULL) Then the interval is TxValue >= StartValue
      -- Also, you should check is this intervals are continuous (using a trigger)
      -- and only the last EndValue IS NULL
      EndValue NUMERIC(18,2) NULL, -- Allow null,
          CONSTRAINT CK_FeeInterval_StartDate_EndDate CHECK (StartValue < EndValue),
      VariableAmount NUMERIC(18,2) NOT NULL, 
      FixedAmount NUMERIC(18,2) NOT NULL
  );
  -- + CREATE INDEX ...
  GO
  
  --DROP TABLE dbo.[Transaction] 
  CREATE TABLE dbo.[Transaction] (
      TransactionID INT IDENTITY(1,1),
          CONSTRAINT PK_Transaction PRIMARY KEY (TransactionID),
      TransactionDate DATE NOT NULL
          CONSTRAINT DF_Transaction_TransactionDate DEFAULT GETDATE(),
      -- You should check (using a trigger) if there is an applicable fee for this TxnValue 
      --      If the last EndValue IS NOT NULL Then TxnValue BETWEEN MIN(StartDate) AND MAX(EndValue) 
      --      Else (the last Endvalue IS NULL Then TxnValue >= MIN(StartDate)
      TxnValue NUMERIC(18,2) NOT NULL
      -- Also, you should store the FeeIntervalID used for every transaction
      -- FeeIntervalID INT NOT NULL 
      -- and (maybe) VariableAmount/FixedAmount/TxnPlusCosts NUMERIC(18,2) NOT NULL
  )
  GO
  
  INSERT dbo.FeeInterval (StartValue, EndValue, VariableAmount, FixedAmount)
  SELECT 0.01, 200,       0.8,    0.1    UNION ALL -- 0.01 because data type is NUMERIC(,2)
  SELECT 201,  300,       0.65,   0.1    UNION ALL
  SELECT 301,  NULL,      0.4,    0.1;
  GO
  
  INSERT dbo.[Transaction] (TxnValue)
  SELECT 0.01 UNION ALL
  SELECT 50 UNION ALL
  SELECT 200 UNION ALL
  SELECT 250 UNION ALL
  SELECT 350 UNION ALL
  SELECT -0.01; -- Wrong value
  GO
  
  --DROP FUNCTION dbo.GetApplicableFee
  CREATE FUNCTION dbo.GetApplicableFee(@TxnValue NUMERIC(18,2))
  RETURNS TABLE
  AS
  RETURN
  SELECT  s.FeeIntervalID, s.VariableAmount, s.FixedAmount, @TxnValue + s.VariableAmount + s.FixedAmount AS TxnPlusCosts
  FROM (
      SELECT  i.FeeIntervalID, i.VariableAmount, i.FixedAmount,
              i.StartValue, i.EndValue,
              ROW_NUMBER() OVER(ORDER BY i.StartValue DESC) RowNum
      FROM    dbo.FeeInterval i
  ) s
  WHERE   @TxnValue BETWEEN s.StartValue AND s.EndValue AND s.RowNum > 1
  OR      @TxnValue >= s.StartValue AND s.RowNum = 1
  GO
  
  SELECT  *,
          CASE WHEN f.FeeIntervalID IS NOT NULL THEN 'TxnValue with applicable fee' ELSE 'Wrong TxnValue' END AS [Description]
  FROM    dbo.[Transaction] txn
  OUTER APPLY dbo.GetApplicableFee(txn.TxnValue) f
  -- or
  SELECT  *, 
          (SELECT f.TxnPlusCosts FROM dbo.GetApplicableFee(txn.TxnValue) f) AS TxnPlusCosts
  FROM    dbo.[Transaction] txn
  

  如果您有任何问题随时问 .

  回复于 2024-05-03T22:15:05+08:00
• 0

  我认为您可以使用oracle过滤器表达式来实现您通过数据库建议的内容而无需更改应用程序...本教程将帮助您实现此目的http://docs.oracle.com/cd/B12037_01/server.101/b10821/expressionconcepts.htm

  回复于 2024-05-03T22:15:05+08:00