sql - 罗马数字作为数据库中的页码

Question

假设我有下表:

create table Section (
    id integer not null primary key,
    book_id integer not null foreign key references Book (id),
    title varchar(100) not null,
    page_start varchar(10) not null,
    page_end varchar(10) not null
    ... remaining fields ...
)

请注意，起始页和结束页字段是 varchars。这样我就可以包括通常使用罗马数字的序言中的页码。

我的问题是:什么是修改此表并编写应用程序的有效方法，以便:

• 我可以根据起始页和结束页对部分进行正确排序，最好使用 SQL
• 我可以用页数计算一个部分的长度
• 我可以确定给定的页码(例如“xviii”或 475)是否在给定的部分内

牢记以下条件/事实:

• 我不希望用户必须输入任何附加信息。例如，他们不必计算前缀的阿拉伯语等效项并将其输入。
• 无论可能存在的书籍预编号规则都将得到严格遵守(例如，所有页码都将以正确的罗马或阿拉伯格式输入)
• 我可以添加任何我需要的额外字段，如果需要甚至可以添加一个单独的表格
• 这是一个网络应用程序，因此我可以在插入或显示数据之前对数据库中的数据进行预处理或后处理
• 部分可以即时添加或删除，例如，可能有一个介绍部分，然后稍后添加另一个部分。该书中所有部分的分页和排序都应保持正确。

我最终可能会在不同的平台上用几种不同的语言实现它，所以代码不可知的伪代码将是首选。

澄清

因为我要处理成千上万条记录，所以我不能仅以编程方式遍历所有记录来执行排序等操作。所以一些工作需要在数据库端进行。

使用 njk 的查找表的想法，我们有类似的东西:

SELECT id, book_id, title, page_start, page_end, 
    COALESCE(RN_Lookup_End.value - RN_Lookup_Start.value + 1, CAST(page_end AS integer)-CAST(page_start AS integer) + 1) as number_of_pages
FROM
    Section 
    LEFT JOIN RN_Lookup AS RN_Lookup_Start ON Section.page_start=RN_Lookup_Start.key
    LEFT JOIN RN_Lookup AS RN_Lookup_End ON Section.page_end=RN_Lookup_End.key
ORDER BY
    book_id, 
    CASE WHEN RN_Lookup_Start.value IS NOT NULL
    THEN -1
    ELSE 0
    END, -- roman page numbers come before normal page numbers
    COALESCE(RN_Lookup_Start.value, page_start), COALESCE(RN_Lookup_End.value, page_end)

如果我想遍历所有按页码排序的书。这样看起来对吗？

想了想，对表格做如下改动会不会更好:

create table Section (
    id integer not null primary key,
    book_id integer not null foreign key references Book (id),
    title varchar(100) not null,
    page_start integer not null,
    page_end integer not null,
    is_front_matter bit default 0,
    page_start_label varchar(10) null,
    page_end_label varchar(10) null
    ... remaining fields ...
)

上面的查询看起来像这样:

SELECT id, book_id, title, 
    COALESCE(page_start_label, CAST(page_start as varchar)) as page_start,
    COALESCE(page_end_label, CAST(page_end as varchar)) as page_end,
    (page_end - page_start + 1) as number_of_pages
FROM
    Section 
ORDER BY
    book_id, is_front_matter DESC, page_start, page_end

然后我所要做的就是在插入和更新时将 page_start_label 和 page_end_label 的值从罗马语转换为阿拉伯语。两个额外的整数加上 bit 意味着每条记录多于 8 个字节的额外存储但是大多数记录都将 page_start_label 和 page_end_label 留空我可能实际上节省空间!

这听起来像是一个合理的解决方案吗？还是我错过了潜在的陷阱/缺点？

Answer 1

虽然我通常会将表示细节留给表示层，但在这种情况下我同意@njk 的观点。

由于您会将罗马数字作为传入数据的一部分，可能需要为多个前端和翻译提供服务，并且您希望能够轻松地在 SQL 中进行排序，因此我会预先计算一个罗马数字查找表数字和它们的等价整数，直到某个相当大的页码(也许是 32,767，尽管您知道您的数据)。

重申一下，我假设页码有一些合理的上限，并且您不会在需要数百万个值的查找表的地方转换任何内容。这足以说服我为此在表示层中使用代码。

貌似已经有any number of functions out there所以您不必重新发明轮子来创建此表。

即使您将来转向不同的方案，使用罗马数字传入数据的想法意味着您可能始终需要这种类型的表才能导入书籍/章节。