Oracle Database Design – Table Design for Multi-Type Data

database-designoracleperformance

The information model I am modelling from includes an "any type". For example a customer can have zero or more 'entries' each of which can have a number of values. Each value could be a either date, numeric, string or spatial. Note query performance is more important than insert performance.

I wondering if you have any ideas for table design.

My ideas are:
(a) a single table encompassing all the types. Each record in entry_data would only use one column for the value. Also each valuetype column needs to be indexed. This seems a little wasteful (although I'm aware oracle won't index the null values )

create table entry ( 
  id number(12) primary key, 
  name varchar2(256) not null unique, 
  datatype varchar2(256) not null, 
  customer_id not null references customer(id)
);

create table entry_data ( 
  id number(12) primary key, 
  entryid number(12) not null references entry(id), 
  strvalue varchar2(256),   -- index
  datevalue date,   -- index
  numvalue number,   -- index
  sdo_geometry geom  -- index
);

OR (b) create a separate entry_data table for each type

create table entry ( 
  id number(12) primary key, 
  name varchar2(256) not null unique, 
  datatype varchar2(256) not null, 
  customer_id not null references customer(id)
);

create table entry_data_str ( 
  id number(12) primary key, 
  entryid number(12) not null references entry(id), 
  strvalue varchar2(256),   -- index
);

-- etc

but I'm not sure if introducing more joins will impact the performance.

Any suggestions welcome.
Thanks.

Best Answer

Not sure why one of the answers made such bold statement . Regardless: everything can be modeled in relational model

create table entry 
( 
  id number(12) primary key, 
  name varchar2(256) not null unique,  
  customer_id number not null references customer(id)
);

create table datatypes
(
 id number primary key,
 name varchar2(256) not null unique
);
create table entry_data ( 
  id number(12) primary key, 
  datatype_id  number not null references datatypes(id),
  value     varchar2(256),  --or CLOB ,no index
  value_geometry geom  ,    -- no index
  --value_blob   blob -- if you neeed, no index
  entry_id number not null references entry(id),
);

your task is related to the multi-valued attributes task. Datatypes table will abstract your datatypes, however 2 value** columns are created in entry_data due to the fact that your data can be different in nature(belong to different Classes), thus would need different storage. But as far as varchar2 vs. number vs.date -- all can be placed into varchar2 , just would need later on some casting. And with very simplistic logic of NVL in code you can determine which column was populated out of value or value_geometry.

Related Question