SQL中书架示意图矩形集合的存储方案合理性与字段设计咨询

场景回顾 #

你需要存储实体书架的示意图，在HTML5 Canvas界面中书架以矩形（由[left, top, right, bottom]坐标定义）形式呈现，每个示意图归属特定用户，且包含多个独立的书架（需分配唯一shelf_id）。

你的现有方案 #

你拟定的两个表结构如下：

schematic表 #

schematic [integer schematic_id, integer user_id]

shelf表 #

shelf [integer shelf_id, integer schematic_id, integer left, integer top, integer right, integer bottom]

方案适用性分析 #

针对你第一个疑问：“每个书架仅属于一个示意图，却需通过WHERE查询获取某示意图的所有书架；若将书架信息并入schematic表，又无法为每个书架分配独立id”
这个方案是完全适合当前场景的，原因如下：

• 这是数据库设计中典型的一对多关系建模：一个用户可以有多个示意图（user_id和schematic_id是一对多），一个示意图可以有多个书架（schematic_id和shelf_id是一对多）。拆分两个表符合数据库范式，能避免数据冗余。
• 通过WHERE schematic_id = ?查询某示意图的所有书架是常规操作，只要给shelf表的schematic_id字段建立索引，查询性能完全有保障。如果把所有书架数据塞进schematic表的某个字段（比如JSON或字符串），反而会导致数据维护困难，也无法高效查询单个书架的信息。

关于坐标字段存储的建议 #

针对你第二个疑问：“将top、right、bottom、left字段替换为单个varchar类型的'[left, top, right, bottom]'字段以简化插入操作，这种做法是否可行？”
这种做法不推荐，理由如下：

• 灵活性缺失：无法单独对某个坐标进行查询（比如“找出所有top坐标小于30的书架”）、排序或数值运算，后续业务扩展会受限。
• 数据校验困难：数据库无法自动校验字符串格式是否合法（比如少写一个数字、格式错误），容易出现脏数据。
• 维护成本高：如果需要修改某个书架的单个坐标（比如调整right值），必须重新替换整个字符串，操作繁琐且容易出错。
• 性能问题：无法为单个坐标字段建立索引，涉及坐标的查询会全表扫描，数据量变大后性能会急剧下降。

如果想简化插入操作，可以在应用层封装插入逻辑（比如写一个函数，把坐标数组拆成四个字段再插入），既保持数据库结构的合理性，又能提升开发效率。

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