将矩阵存储在关系数据库中 [英] Storing matrices in a relational database

问题描述

我正在为客户开展一个项目，并通过初始的数据库设计。该项目将是一个简单的Web应用程序，用于在矩阵图中跟踪进程及其结果，我正在寻找一种将这些存储在关系表中的好方法。

现在我想我有一个常规表，用于x和y坐标也将映射的例程，也可能是一个包含坐标ID的查找表记录了打。任何人都有这样做的更好的方法？

谢谢！

编辑：

这只是项目的开始，所以我还有一些细节，但是我的主要原因在于多个表是因为矩阵将是完全动态的大小和通用的，所以每个可能是不同的，他们将被绑定到一个用户

我也忘记提到x / y值的顺序很重要，这进一步支持我的推理背后有多个表xy和值，从此我强烈地认为，需要知道每一个单元格是重要的

示例：

这个基本的例子（虽然是抽象的）在于关于餐厅的过程。行动是沿着坐着的路线，订购食物，查看菜单，订购饮料，吃饭，支付等等，结果是订购，饮料交付，食物交付，更改。虽然看起来很简单，但考虑到每一次发生的事情都会发生不同的情况，而且在取出或自助餐的情况下也会变得复杂。

解决方案

有很多方法可以做到这一点，我们需要更多的信息来更具体地了解最适合您的信息。但是，以下是两种SOP方式：

每个矩阵的单独表格：

  CREATE TABLE YourMatrixName（
 RowNo smallint NOT NULL，
 ColNo smallint NOT NULL，
 CellValue varchar]（50）NULL，
 CONSTRAINT [PK_Matrices] PRIMARY KEY CLUSTERED 
（[RowNo] ASC，[ColNo] ASC）
）ON [PRIMARY]; 
 GO 
 
 CREATE UNIQUE NONCLUSTERED INDEX IX_YourMatrixName ON dbo.YourMatrixName 
（ColNo，RowNo）; 
 GO 
  

或者，一个表中的所有矩阵：

  CREATE TABLE矩阵（
 MatrixName varchar（24）NOT NULL，
 RowNo smallint NOT NULL，
 ColNo smallint NOT NULL，
 CellValue varchar（50）NULL，
 CONSTRAINT [PK_Matrices] PRIMARY KEY CLUSTERED 
（[MatrixName] ASC，[RowNo] ASC，[ColNo] ASC）
）ON [主]; 
 GO 
 
 CREATE UNIQUE NONCLUSTERED INDEX IX_Matrices ON dbo.Matrices 
（ColNo，RowNo）; 
 GO 
  

---

这些是标准正常形式几乎所有其他的做法都没有很好的正常化。这些方法的一些优点：

1. 您不必填写每个单元格，只能填充您正在使用的单元格。或者有一个默认值（0或），然后跳过这些。


2. 这是很灵活的方法，即使是在一体化模式中，也不需要限制他们以任何方式达到相同的大小，并且很容易调整他们的大小。


3. 您可以轻松查询 内容 矩阵，在更紧凑的存储方法中越来越困难。


4. 命中或矩阵单元格的任何其他方面都可以轻松地作为行中的其他字段来实现。如果您担心额外的空间，请将其设为无效，如果要单独查询/报告这些属性，请对其进行索引。它也很容易改装这样的功能，这个模型也。

主要缺点是通常有很高的空间数据开销。许多人认为插入或检索新矩阵的开销也很高，但实际上有几种记录的技术可以使其相当快。

I am working on a project for a client and going through the initial database design. The project will be a simple web app for tracking processes and their outcomes within a matrix diagram, I am looking for a good way to store these in relational tables.

Right now I am thinking I have a general table for Routines which the x and y coords will map too and maybe off from that a lookup table containing the ID of coordinates in which a "hit" is recorded. Anyone have any better ways of doing this?

Thanks!

EDIT:

This is just the beginning of the project so I have limited detail as of yet, but my main reasoning behind multiple tables is because the matrices will be completely dynamic in size and generic so that each one may be different and they will be tied to a user

I also forgot to mention that order of the x/y values are important, which further supported my reasoning behind having multiple tables for x y and values, from this I strongly assume that needing to know each individual cell is important

EXAMPLE:

The basic example (albeit abstract) of this lies in the process regarding a restaurant. The actions being stuff along the lines of sit down, order food, look over menu, order drinks, eat, pay, etc. the outcomes being order taken, drinks delivered, food delivered, change given. While seemingly simple it becomes complex when taken into consideration things happen differently with each occurrence, also in the case of take out or buffets. the order of the actions and outcomes becomes integral in seeing the differences between the situations

解决方案

There are lots of way to do this, we would need a lot more information to be more specific about what would be best for you. However, here are the two SOP ways:

Either a separate table for each matrix:

CREATE TABLE YourMatrixName(
    RowNo smallint NOT NULL,
    ColNo smallint NOT NULL,
    CellValue varchar](50) NULL,
 CONSTRAINT [PK_Matrices] PRIMARY KEY CLUSTERED 
    ([RowNo] ASC, [ColNo] ASC)
) ON [PRIMARY];
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_YourMatrixName ON dbo.YourMatrixName
    (ColNo, RowNo); 
GO

Or, all of the matrices in one table:

CREATE TABLE Matrices(
    MatrixName varchar(24) NOT NULL,
    RowNo smallint NOT NULL,
    ColNo smallint NOT NULL,
    CellValue varchar(50) NULL,
 CONSTRAINT [PK_Matrices] PRIMARY KEY CLUSTERED 
    ([MatrixName] ASC, [RowNo] ASC, [ColNo] ASC)
) ON [PRIMARY];
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_Matrices ON dbo.Matrices
    (ColNo, RowNo); 
GO

---

These are standard normal form, virtually all other ways of doing it are not well normalized. Some advantages of these approaches:

1. You do not have to fill in every cell, only the ones you are using. Or have a default value (0 or "") and skip those.
2. This is easily the most flexible approach, even in the "all in one" model, there is no need to restrict them to the same size in any way, and it is very easy to resize them.
3. You can easily query the contents of the matrix, something that is increasingly difficult in more compact storage methods.
4. "Hit"s or any other aspect of the matrix cells are easy to implement as additional fields in the rows. Make them Null-able if you're worried about the additional space, and index them if you want to query/report on these attributes separately. Its also just as easy to retrofit features like this with this model also.

The primary disadvantage is that there is typically a high space to data overhead. Many assume that there is also high overhead to Insert or retrieve new matrices but in fact there are several documented techniques that can make it quite fast.

这篇关于将矩阵存储在关系数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！