관계형 데이터베이스의 사용자 정의 필드 설계 패턴

관계형 데이터베이스의 사용자 정의 필드 설계 패턴

저는 (비교적) 간단한 보고 시스템을 만드는 일을 맡겼습니다.이 시스템에서는 사용자에게 보고 결과 테이블이 표시됩니다.테이블에는 일부 필드가 있으며 각 필드는 각 레코드의 사용자에게 일부 정보를 제공합니다.하지만 저의 문제는 각 보고서 필드가 개발자에 의해 선언되지 않는다는 것입니다.시스템 사용자가 이를 선언해야 합니다.그래서 제 보고서 표는 역동적입니다.

ASP에서 'Data Driven Custom View Engine'의 예를 보았습니다.Asp.net MVC Framework를 사용하여 동적 양식을 만드는 'NET MVC'를 사용하지만 그것이 내 시스템에 적합한지 아닌지 모르겠습니다.

업데이트 1:

현재 다음 개체 관계 다이어그램으로 끝냈습니다.

의 도표에서 는 합니다 에 하고 있습니다.Report table에 를 저장합니다. 또한 보고서 유형을 저장합니다.ReportType각a. 는 를입니다를 입니다.ReportFieldValue이 . 됩니다.ReportField.

내 하려면 db 합니다 합니다.Report표.하겠습니다.ReportFieldValue블.

그러나 당신이 알아차릴 수 있듯이, 이 방법에서 나는 모든 필드 값을 char(255)로 저장해야 합니다.입니다와 입니다.datetime문자열로 저장해서는 안 됩니다.이러한 유형의 시스템에 대한 설계 패턴이나 아키텍처가 있습니까?

를) 대체하여 문자열 합니다.VALUE와 함께NUMBER_VALUE,DATE_VALUE,STRING_VALUE 그 세 가지 유형은 대부분 충분히 좋습니다.나중에 필요한 경우 XMLTYPE 및 기타 멋진 열을 추가할 수 있습니다.Oracle의 경우 공간을 절약하기 위해 CHAR 대신 VARCHAR2를 사용합니다.

항상 값을 올바른 유형으로 저장하려고 합니다.네이티브 데이터 유형은 더 빠르고, 더 작고, 사용하기 쉽고, 더 안전합니다.

Oracle에는 일반 데이터 유형 시스템(ANYTYPE, ANYDATA 및 ANYDATA)이 있지만 이러한 유형은 사용하기 어려우므로 대부분의 경우 사용하지 않는 것이 좋습니다.

설계자들은 종종 모든 데이터에 대해 단일 필드를 사용하는 것이 일을 더 쉽게 만든다고 생각합니다.이를 통해 데이터 모델의 예쁜 사진을 쉽게 생성할 수 있지만 다른 모든 것은 더욱 어려워집니다.다음과 같은 문제를 고려합니다.

1. 자료를 가지고 흥미로운 일을 할 때는 유형을 알지 못하면 할 수 없습니다.데이터를 표시하는 경우에도 텍스트를 정당화하는 유형을 아는 것이 유용합니다.모든 사용 사례의 99.9%에서 사용자는 3개의 열 중 어느 것이 적절한지를 알 수 있습니다.

2. 문자열 형식의 데이터에 대한 안전한 쿼리를 개발하는 것은 어려운 일입니다.예를 들어, 여러분이 이 천년에 태어난 사람들의 "생년월일"을 찾고 싶다고 가정해 보겠습니다.

   select *
   from ReportFieldValue
   join ReportField
       on ReportFieldValue.ReportFieldid = ReportField.id
   where ReportField.name = 'Date of Birth'
       and to_date(value, 'YYYY-MM-DD') > date '2000-01-01'
   

   벌레를 찾을 수 있습니까?위 쿼리는 날짜를 올바른 형식으로 저장했더라도 위험하며, 이를 제대로 수정하는 방법을 아는 개발자는 극소수입니다.Oracle은 특정 작업 순서를 강제하기 어려운 최적화 기능을 가지고 있습니다.안전하려면 다음과 같은 쿼리가 필요합니다.

   select *
   from
   (
       select ReportFieldValue.*, ReportField.*
           --ROWNUM ensures type safe by preventing view merging and predicate pushing.
           ,rownum
       from ReportFieldValue
       join ReportField
           on ReportFieldValue.ReportFieldid = ReportField.id
       where ReportField.name = 'Date of Birth'
   )
   where to_date(value, 'YYYY-MM-DD') > date '2000-01-01';
   

   모든 개발자에게 쿼리를 그런 식으로 작성하라고 말할 필요는 없습니다.

설계는 EAV(Entity Attribute Value) 데이터 모델의 변형으로, 데이터베이스 설계에서 종종 반패턴으로 간주됩니다.

예를 들어, 300개의 열(NUMBER_VALUE_1 ~ NUMBER_VALUE_100, VARCHAR2_VALUE_1..100 및 DATE_VALUE_1..100)로 보고 값 테이블을 생성하는 것이 더 나은 방법일 수 있습니다.

그런 다음, 어떤 보고서가 어떤 열을 사용하고 무엇에 각 열을 사용하는지 추적하는 것을 중심으로 나머지 데이터 모형을 설계합니다.

여기에는 두 가지 이점이 있습니다. 첫째, 날짜와 숫자를 문자열로 저장하지 않는다는 점(이미 설명한 이점)과 둘째, EAV 모델과 관련된 많은 성능 및 데이터 무결성 문제를 방지한다는 점입니다.

EDIT - EAV 모형의 일부 경험적 결과 추가

Oracle 11g2 데이터베이스를 사용하여 하나의 테이블에서 30,000개의 레코드를 EAV 데이터 모델로 이동했습니다.그리고 나서 모델에 문의를 해서 3만 개의 레코드를 되찾았습니다.

SELECT SUM (header_id * LENGTH (ordered_item) * (SYSDATE - schedule_ship_date))
FROM   (SELECT rf.report_type_id,
               rv.report_header_id,
               rv.report_record_id,
               MAX (DECODE (rf.report_field_name, 'HEADER_ID', rv.number_value, NULL)) header_id,
               MAX (DECODE (rf.report_field_name, 'LINE_ID', rv.number_value, NULL)) line_id,
               MAX (DECODE (rf.report_field_name, 'ORDERED_ITEM', rv.char_value, NULL)) ordered_item,
               MAX (DECODE (rf.report_field_name, 'SCHEDULE_SHIP_DATE', rv.date_value, NULL)) schedule_ship_date
        FROM   eav_report_record_values rv INNER JOIN eav_report_fields rf ON rf.report_field_id = rv.report_field_id
        WHERE  rv.report_header_id = 20 
        GROUP BY rf.report_type_id, rv.report_header_id, rv.report_record_id)

결과는 다음과 같습니다.

1 row selected.

Elapsed: 00:00:22.62

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                        | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                             |     1 |  2026 |    53  (67)|
|   1 |  SORT AGGREGATE                 |                             |     1 |  2026 |            |
|   2 |   VIEW                          |                             |   130K|   251M|    53  (67)|
|   3 |    HASH GROUP BY                |                             |   130K|   261M|    53  (67)|
|   4 |     NESTED LOOPS                |                             |       |       |            |
|   5 |      NESTED LOOPS               |                             |   130K|   261M|    36  (50)|
|   6 |       TABLE ACCESS FULL         | EAV_REPORT_FIELDS           |   350 | 15050 |    18   (0)|
|*  7 |       INDEX RANGE SCAN          | EAV_REPORT_RECORD_VALUES_N1 |   130K|       |     0   (0)|
|*  8 |      TABLE ACCESS BY INDEX ROWID| EAV_REPORT_RECORD_VALUES    |   372 |   749K|     0   (0)|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("RV"."REPORT_HEADER_ID"=20)
   8 - filter("RF"."REPORT_FIELD_ID"="RV"."REPORT_FIELD_ID")

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
     275480  consistent gets
        465  physical reads
          0  redo size
        307  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

각각 4열씩 3만 줄을 얻는데 22초가 걸립니다.그것은 너무 깁니다.평평한 테이블에서 2초도 안걸렸을거에요, 쉽죠.

동적 열과 함께 MariaDB를 사용합니다.이를 통해 잡종 열을 하나의 열에 모두 넣을 수 있으면서도 효율적으로 액세스할 수 있습니다.

저는 일반적인 분야들 중 몇 개는 그들 자신의 칼럼에 보관할 것입니다.

EAV에 대한 자세한 논의 및 제안(그리고 동적 열 없이 수행하는 방법)

데이터를 정확한 데이터 유형에 저장하는 것에 대해 매우 좋은 점이 있습니다.
그리고 이는 사용자 정의 데이터 시스템에 문제가 된다는 것에 동의합니다.

이 문제를 해결하기 위한 한 가지 방법은 각 데이터 유형 그룹(ints, 부동 소수점, 문자열, 이진수 및 날짜)에 대한 테이블을 추가하는 것입니다. 값을 다음과 같이 유지하는 대신ReportFieldValue테이블. 하지만, 이것은 여러분이 하나의 결과를 얻기 위해서 여러 개의 테이블을 선택하고 결합해야 하기 때문에 여러분의 삶을 더 힘들게 할 것입니다.

다른 방법은 데이터 유형 열을 에 추가하는 것입니다.ReportFieldValue문자열에서 적절한 데이터 유형으로 동적으로 캐스트하는 사용자 정의 함수를 만들어 정렬, 검색 등에 사용할 수 있도록 합니다.

Sql server는 여러 유형을 지원해야 하는 데이터 유형도 있으며, 문서 작업을 해본 적은 없지만 가능성이 있는 것 같습니다.

언급URL : https://stackoverflow.com/questions/31721343/design-pattern-for-custom-fields-in-relational-database