1

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.

  • If you create a separate entry_data_*, I think you'd have a problem where you first must query to find out which entry_data_* table to query and then actually query entry_data_*. – FrustratedWithFormsDesigner Jun 12 '14 at 15:34
  • You say that query performance is important. What would be typical queries? Give me all customers that have an entry_date within the last month (and have an entry_num > 7)? Give me all entry_data for customer X? What is the number of entries for all customers? – AHalvar Jun 13 '14 at 8:57
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.

  • I agree with your comments about the relational model but not with your model. Having one column (Varchar or Text or Blob) to store every possible value is not good, in my opinion. You can't have enforce constraints this way. And if you cast your values for every query, that will kill performance. – ypercubeᵀᴹ Jun 17 '14 at 15:28
  • Well - if you do want to preserve "type" then question comes down: what is your anticipated amount of types (or in the context of question amount of attributes) ? 2,3,5 .... or 100 if "former" [2,3,5], then table can be easily pivoted, otherwise you ended up with looong table. – MathCurious Jun 17 '14 at 15:35
0

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.

  • In connection with this answer, it would be useful for Richard to visit the tag "class-table-inheritance" over in SO and read the info tab. There's a more detailed outline of choices 2 and 3 above. – Walter Mitty Jun 13 '14 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.