DB设计用于存储表的自定义字段 [英] DB Design to store custom fields for a table

问题描述

这个问题是根据我对这个问题的回应而出现的：
在Oracle中使用TO_NUMBER函数出现奇怪的问题

正如所有人建议在VARCHAR2列中存储Numeric值不是一个好习惯我完全同意），我想知道我们团队所做的基本设计选择，以及是否有更好的设计方式。

问题陈述：我们有很多表格我们要给定一定数量的自定义字段。已知所需的自定义字段的数量，但是用户可以使用什么样的属性映射到列。

例如。假设您有一台笔记本电脑存储每笔笔记本电脑记录的50个属性值，那么我会放下一个假设的情景。

每个笔记本电脑属性由创建笔记本电脑的一些管理员创建。

一个用户创建了一个笔记本电脑产品，让lap1与属性String，String，numeric，numeric，String

第二个用户创建笔记本电脑lap2与属性字符串，数字，字符串，字符串，数字

目前我们的数据设计会持续如下

 笔记本电脑桌
 Id名称field1 field2 field3 field4 field5 
 1 lap1 lappy lappy 12 13 lappy 
 2 lap2 lappy2 13 lappy2 lapp2 12 
  

此示例模拟我们的要求和我们的设计

现在在这里，如果有人正在查找lap2表，在field2上进行比较，我们需要申请TO_NUMBER。

  select * from laptop 
其中name ='lap2'
和TO_NUMBER（field2）<在某些情况下，当查询计划决定首先应用to_number而不是其他过滤器时，$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ / p> 
 
 
问题
 
这是一个有效的设计吗？
 
解决这个问题的其他替代方法是什么？
 
我们的一个队友建议在飞行中为这种情况创建表。这是一个好主意吗？
 
流行的ORM工具如何给定制字段或Flex字段处理？
 
 
 
我希望我能够有意义这个问题。
 
 
 
对于这么长的文本，对不起。
 
 
 
这样我们可以在查询时使用TO_NUMBER / p> 
 
解决方案
这是一个常见的问题，没有完美的解决方案。一些解决方案：
 
 
 
  1。 
定义类型为varchar2的X字段，类型数字和Z字段的Y字段日期。这可能是自定义字段数量的3倍，但您将永远不会有任何转换问题。
 
 
 
您的示例将出现如下：
  Id名称field_char1 field2_char2 field_char3 ... field_num1 field_num2 ... 
 1 lap1 lappy lappy lappy ... 12 13 
 2 lap2 lappy2 lappy2 lapp2 ... 13 12 
  
在您的示例中，您具有相同数量的值和两行的字符值，但不一定是这样的：例如，第三行可能没有数字字段。
 
 
 
  2。 strong> 
定义类型为varchar2的X字段，并应用双击函数存储号码或日期字段（例如，日期可以存储为 YYYYMMDDHH24miss ）。您还需要一个额外的字段来定义行的上下文。  c $ c> to_number 或 to_char 函数只有当行是好的类型。
 
 
 
您的示例：
  Id名称上下文field1 field2 field3 field4 field5 
 1 lap1类型A lappy lappy 12 13 lappy 
 2 lap2 type B lappy2 13 lappy2 lapp2 12 
  
你可以使用DECODE或CASE来查询表：
  SELECT * 
 FROM laptop 
 WHERE CASE WHEN context = 'TYPE A'THEN to_number（field3）END = 12 
  
第二个设计是Oracle Financials ERP（等）。上下文允许您使用此设计定义CHECK约束（例如 CHECK（CASE WHEN context ='TYPE A'THEN to_number（field3）> 0 ）以确保完整性。 
 
this question came up based on the responses I got for the question
Getting weird issue with TO_NUMBER function in Oracle

As everyone suggested that storing Numeric values in VARCHAR2 columns is not a good practice (which I totally agree with), I am wondering about a basic Design choice our team has made and whether there are better way to design.

Problem Statement : We Have many tables where we want to give certain number of custom fields. The number of required custom fields is known, but what kind of attribute is mapped to the column is available to the user

E.g. I am putting down a hypothetical scenario below

Say you have a laptop which stores 50 attribute values for every laptop record. Each laptop attributes are created by the some admin who creates the laptop.

A user created a laptop product lets say lap1 with attributes String, String, numeric, numeric, String

Second user created laptop lap2 with attributes String,numeric,String,String,numeric

Currently there data in our design gets persisted as following
Laptop Table
Id Name field1  field2 field3  field4 field5
1  lap1 lappy   lappy  12      13     lappy
2  lap2 lappy2  13     lappy2  lapp2  12
This example kind of simulates our requirement and our design

Now here if somebody is lookinup records for lap2 table doing a comparison on field2, We need to apply TO_NUMBER.
select * from laptop 
where name='lap2'  
and TO_NUMBER(field2) < 15
TO_NUMBER fails in some cases when query plan decides to first apply to_number instead of the other filter.

QUESTIONS

Is this a valid design?

What are the other alternative ways to solve this problem?

One of our team mates suggested creating tables on the fly for such cases. Is that a good idea?

How do popular ORM tools give custom fields or flex fields handling?

I hope I was able to make sense in the question.

Sorry for such a long text.. 

This causes us to use TO_NUMBER when queryio
解决方案
This is a common problem and there is no perfect solution. A couple of solutions:

1.
 Define X fields of type varchar2, Y fields of type number and Z fields of type date. That comes out as potentially 3 times the number of custom fields but you will never have any conversion problem anymore.

Your example would come out like this:
Id Name field_char1  field2_char2 field_char3 ... field_num1 field_num2 ...
1  lap1 lappy        lappy        lappy       ... 12         13     
2  lap2 lappy2       lappy2       lapp2       ... 13         12
In your example you have the same number of numeric values and character values on both rows but it doesn't have to be this way: the third row could have no numeric field for example.

2.
 Define X fields of type varchar2 and have apply a bijective function to store number or date field (for example Date could be stored as YYYYMMDDHH24miss). You will also need an extra field that will define the context of the row. You would apply the to_number or to_char function only when the rows are of the good type.

Your example:
Id Name context field1  field2 field3  field4 field5
1  lap1 type A  lappy   lappy  12      13     lappy
2  lap2 type B  lappy2  13     lappy2  lapp2  12
You could query the table using DECODE or CASE:
SELECT * 
  FROM laptop
 WHERE CASE WHEN context = 'TYPE A' THEN to_number(field3) END = 12
The second design is the one used in the Oracle Financials ERP (among others). The context allows you to define CHECK constraints with this design (for example CHECK (CASE WHEN context = 'TYPE A' THEN to_number(field3) > 0) to ensure integrity.

                        
这篇关于DB设计用于存储表的自定义字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！