最佳经验:将不同类型的数据存储在Oracle数据库中时,可以考虑以下几点:

huangapple go评论60阅读模式
英文:

Best experiences to storing different types of data in an Oracle database

问题

好晚上

我目前正在开发一个Java应用程序。现在我想让用户为一个对象保存无限数量的关联附件。

这些附件可以是以下类型之一:字符串、XML文件、二进制文件。

我使用Oracle数据库作为数据库。实际上,每种类型推荐使用不同的数据类型:
字符串 - VARCHAR
XML文件 - CLOB
二进制文件 - BLOB

这里最好的方式是什么?是一个通用表,其中包含了这3个字段(varchar、clob、blob),然后根据类型有两个字段是NULL,还是三个单独的表?

期待您的回答

英文:

Good evening

I am currently developing a Java application. Now I want to enable the user to save an indefinite number of associated attachments for a object.

These attachments can be of the following types: string, xml file, binary files.

I use an Oracle db as the database. A different data type is actually recommended for each type:
String - VARCHAR
XML file - CLOB
Binary file - BLOB

What's the best way here? A general table, which among other things contain the 3 fields (varchar, clob, blob) and depending on the type are two fields then NULL or three separate tables?

Am looking forward to your answers

答案1

得分: 1

你不必担心通过在单个表中为每个广泛分类的VARCHAR2CLOBBLOB使用三个不同列来浪费空间的问题。Oracle在这样做时不会使用任何(有意义的)大量空间,如果你有3个表,那只会使事情变得更复杂。你已经为这些文件内容类型选择了合适的数据类型。

我还会添加一个附加列,FILE_TYPE VARCHAR2(10),其中可以包含文件类型,以方便起见。并且该列将通过一个简单的内联约束进行限制,以验证FILE_TYPE

create table user_files (
    file_name varchar2(4000) not null, 
    , file_type varchar2(8) not null check (file_type in ('VARCHAR2', 'CLOB', 'BLOB'))
    , text_data varchar2(4000)
    ,xml_data  clob
    , binary_data blob
   );
英文:

You don't have to worry about "wasted" space by having a single table with three different columns for each broad classification of VARCHAR2, CLOB, and BLOB. Oracle won't use any (meaningful) amount of space doing this, and if you had 3 tables, that would only complicate things. You have picked the appropriate data types for those types of file content.

I would also have an additional column, FILE_TYPE VARCHAR2(10) that could have the file type in it, for convenience. And that column would be limited via a simple inline constraint to validate the FILE_TYPE:

create table user_files (
    file_name varchar2(4000) not null, 
    , file_type varchar2(8) not null check (file_type in ('VARCHAR2', 'CLOB', 'BLOB'))
    , text_data varchar2(4000)
    ,xml_data  clob
    , binary_data blob
   );

huangapple
  • 本文由 发表于 2020年9月29日 02:49:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/64107958.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定