英文:
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
你不必担心通过在单个表中为每个广泛分类的VARCHAR2
,CLOB
和BLOB
使用三个不同列来浪费空间的问题。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
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论