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

user_13985284 0
Viewed 55

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

data-structures oracle java
1 Answers

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
   );
user_41782620
Related