sql - 使用 Loan 和 LoanLines 进行数据库设计

问题描述

几天来我一直无法想出一个可行的解决方案。

我正在开发一个系统来维护物品并借出这些物品。

Loan包含IEnumerable<LoanLine>其中每个指向Item：

到目前为止，一切都很好。当每件物品不能在同一时期借出时，棘手的部分就暴露出来了。那个时期由定义LoanLine.PickedUp ?? Loan.DateFrom > LoanLine.Returned ?? Loan.DateTo。这意味着如果LoanLine.PickedUp为空，Loan.DateFrom则应使用比较，如果LoanLine.Returned为空，Loan.DateTo则应使用。可以在外借范围外取回物品。所以可能会出现这些场景：

也应该可以“返回”，即。设置LoanLine.Returned为null，在这种情况下Loan.DateTo用于再次比较。也是如此LoanLine.PickedUp。也应该可以同时更新Loan.DateFrom和Loan.DateTo，而前面提到的约束仍然有效。这意味着如果更新Loan导致其中任一行DateTime设置为空的行重叠，则约束将引发错误。

这是创建脚本：

create table loan
(
    id                  int             primary key identity(1, 1),
    datefrom            date            not null,
    dateto              date            not null,
    employee_id         int             references employee(id) not null,
    recipient_id        int             references employee(id) null,
    note                nvarchar(max)   not null,
    constraint c_loan_chkdates check (datefrom <= dateto)
);

create table loanlineitem
(
    id                  int             primary key identity(1, 1),
    loan_id             int             references loan(id) on delete cascade not null,
    item_id             int             references item(id) not null,
    pickedup            datetime        null,
    returned            datetime        null,
    constraint uq_loanlineitem unique (loan_id, item_id),
    constraint c_loanlineitem_chkdates check (returned is null or pickedup <= returned)
);

这是约束：

create function checkLoanLineItem(@itemId int, @loanId int, @pickedup datetime, @returned datetime)
returns bit
as
begin
    declare @result bit = 0;
    declare @from date = @pickedup;
    declare @to date = @returned;
    
    --If either @from or @to is null, fill the ones with null from loan-table
    if (isnull(@from, @to) is null)
    begin
        select  @from = isnull(@from, datefrom),
                @to = isnull(@to, dateadd(d, 1, dateto))
        from    loan
        where   id = @loanId;
    end

    if not exists (select top 1 lli.id from loanlineitem lli
        inner join loan l on lli.loan_id = l.id
        where l.id <> @loanId
        and lli.item_id = @itemId
        and ((isnull(lli.pickedup, l.datefrom) >= @from and isnull(lli.pickedup, l.datefrom) < @to)
            --When comparing datetime with date, the date's time is 00:00:00
            --so one day is added to account for this
            or (isnull(lli.returned, dateadd(d, 1, l.dateto)) >= @from and isnull(lli.returned, dateadd(d, 1, l.dateto)) < @to))
        )
    begin
        set @result = 1;
    end

    return @result;
end;

go;

alter table loanlineitem
add constraint c_loanlineitem_checkoverlap check (dbo.checkLoanLineItem(item_id, loan_id, pickedup, returned) = 1)

go;

我可以在 -table 上做一个类似的约束，Loan但是如果可能的话，我会在两个地方有类似的代码，我宁愿避免。

所以我要问的是；我应该重新考虑我的架构来实现这一点，还是有一些我不熟悉的约束？

标签： sqlsql-serverdatabase-designcheck-constraints

解决方案

为此，我们需要两件事：

一种跟踪有关贷款的项目状态的方法
在某个时间点只允许一笔有效贷款

第一项可以通过数据模型解决（见下文），但第二项将需要对数据库的任何更改都必须通过存储过程进行，并且这些存储过程必须包含逻辑以使数据库保持一致状态。否则，您的手会一团糟（或依赖触发器，这是另一个令人头疼的问题）。

我们将通过基于时间戳的项目状态跟踪项目的物理状态，如果需要，我们将通过基于未来日期的另一种机制进行预订。

此查询将返回所有项目的当前状态和贷款，以及下一次预订。由此您还可以确定哪些项目已逾期。

SELECT
  Item.ItemId
 ,ItemStatus.UpdateDtm
 ,ItemStatus.StatusCd
 ,ItemStatus.LoanNumber
 ,Loan.StartDt
 ,Loan.EndDt
 ,Reservation.StartDt
 ,Reservation.EndDt
FROM
  Item Item
LEFT JOIN
  LoanItemStatus ItemStatus
    ON ItemStatus.ItemId = Item.ItemId
        AND ItemStatus.UpdateDtm =
              (
                SELECT
                  MAX(UpdateDtm)
                FROM
                  LoanItemStatus
                WHERE
                  ItemId = Item.ItemId
              )
LEFT JOIN
  Loan Loan
    ON Loan.LoanNumber = ItemStatus.LoanNumber
LEFT JOIN
  ItemReservation Reservation
    ON Reservation.ItemId = Item.ItemId
        AND Reservation.StartDt =
              (
                SELECT
                  MIN(StartDt)
                FROM
                  ItemReservation
                WHERE
                  ItemId = Item.ItemId
                    AND StartDt >= GetDate()
              )

将这种逻辑硬化成一个视图可能是有意义的。

要查看在给定时间范围内是否保留项目：

SELECT
  Item.ItemId
 ,CASE
    WHEN COALESCE(PriorReservation.EndDt,GETDATE()) <= @ReservationStartDt AND @ReservationEndDt <= COALESCE(NextReservation.StartDt,'9999-12-31') THEN 'Y'
    ELSE 'N'
  END AS ReservationAvailableInd
FROM
  Item Item
LEFT JOIN
  ItemReservation PriorReservation
    ON PriorReservation.ItemId = Item.ItemId
        AND PriorReservation.StartDt =
              (
                SELECT
                  MAX(StartDt)
                FROM
                  ItemReservation
                WHERE
                  ItemId = Item.ItemId
                    AND StartDt <= @ReservationStartDt
              )
LEFT JOIN
  ItemReservation NextReservation
    ON NextReservation.ItemId = Item.ItemId
        AND NextReservation.StartDt =
              (
                SELECT
                  MIN(StartDt)
                FROM
                  ItemReservation
                WHERE
                  ItemId = Item.ItemId
                    AND StartDt > @ReservationStartDt
              )

因此，您需要将所有这些都滚动到您的存储过程中，以便：

当一个项目被借出时，它在指定的时间段内可用
更改外借日期范围时，它不会与现有项目或未来预订冲突
当进行新的保留时，它们不会与现有的程序保留冲突
状态转换是有意义的（未借出/归还 -> 等待取货 -> 取货 -> 归还/丢失）
您不能删除已取货或已取货的外借

sql - 使用 Loan 和 LoanLines 进行数据库设计

问题描述

解决方案

推荐阅读