Table design for multi-type data

Question

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.

Answer 1

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.

Answer 2

Disclaimer: What you are trying to do is not a good fit for the relational model: You want to store data of any type (the BLOB datatype would theoretically do) and you want to be able to query those data with good performance (a specific datatype is required to allow indexing). These are contradicting requirements for an RDBMS but fine for NoSQL-databases. I am aware that many projects require a key-value database that shall be open for storing anything ("We don't know yet which properties our clients want to store"), but insist on using a RDBMS for it. That won't work in the long run: Been there, done that against better knowledge. Use the right tool for the job.

---

However, if your domain model says: A customer can have many entries. An entry belongs to exactly one customer. and: An entry can be either a number, a date, a string xor a spatial, then you can model this, by way of Extended Entity Relationship-Modeling, in three different ways, of which you have already identified two.

1. Much like your choice (a), but using a single table:

   create table entry ( 
     id number(12) primary key, 
     customer_id number(12) not null references customer(id),
     strvalue varchar2(256),   -- index
     datevalue date,   -- index
     numvalue number,  -- index
     geomvalue geom,   -- index
     entryUsed varchar2(1) not null, -- bitmap-index, denoting which value column is used
    );
   

2. Your choice (b), having an intermediate table entry and subtables entry_num etc.

3. Omit the intermediate table entry and only have tables entry_num etc. where each table has a foreign key to the customer table.

   create table entry_num ( 
     id number(12) primary key, 
     customer_id number(12) not null references customer(id),
     numvalue number,   -- index
   );
   

You choose which to implement by looking at the queries that you need to do.