0

I have a table in Oracle Database which has 60 columns. Following is the table structure. 

ID  NAME TIMESTAMP PROERTY1 ...... PROPERTY60

This table will have many rows. the size of the table will be in GBs. But the problem with the table structure is that in future if I have to add a new property, I have to change the schema. To avoid that I want to change the table structure to following. 

ID NAME TIMESTAMP PROPERTYNAME PROPERTYVALUE

A sample row will be. 

1  xyz  40560 PROPERTY1 34500

In this way I will be able to solve the issue but the size of the table will grow bigger. Will it have any impact on performance in terms on fetching data. I am new to Oracle. I need your suggestion on this.

APC
  • 144,005
  • 19
  • 170
  • 281
user2032118
  • 455
  • 3
  • 6
  • 16
  • Why will you have to change the schema to add a new property column to your table? – WoMo Feb 20 '13 at 06:10
  • Lets say I want to support "PROPERTY61" in future, in that case I have to change the schema if I go with first approach. – user2032118 Feb 20 '13 at 06:13
  • This is close to a scheme called EAV [Entity, Attribute, Value](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model). It is generally a bad idea for quite a variety of reasons. This doesn't stop people using it, but it becomes very hard to query, and even harder to ensure the data is self-consistent. – Jonathan Leffler Feb 20 '13 at 06:14
  • 1
    `ALTER TABLE ADD COLUMN ...` is not an heavy change, or is it? If programmers do not use `SELECT *`you shouldn't have any problem in the future. – Plouf Feb 20 '13 at 09:36
  • 1
    When I said "heavy change", I meant it in terms of application code changes and upgrade effort. – user2032118 Feb 20 '13 at 10:13
  • If you don't have to change the code to use a new attribute what is the value of the new attribute? – APC Feb 20 '13 at 10:25
  • Anyway, the main point is, if you think "application code changes and upgrade effort" is a heavy change, wait until you try to build an application entirely out of metadata and dynamic SQL. That's heavy. – APC Feb 20 '13 at 10:26
  • There's a trem,endous value in database schemas, as they provide data integrity, type validation and immense performance benefits from joining. But if you seriously want to build an application based on KV pairs don't use Oracle. Why spend all that money on something you're not going to use properly? Choose a schemaless database, there are dozens of them about. http://nosql-database.org/ – APC Feb 20 '13 at 10:29
  • possible duplicate of [Data Modeling for EAV](http://stackoverflow.com/questions/1689459/data-modeling-for-eav) – APC Feb 20 '13 at 10:31
  • I disagree with this being an EAV pattern. This is a simple repeating group, thus it's a basic normalisation problem. The column names (NAME/VALUE) are reminiscent of EAV, but they're not actually EAV. It would be better if they were called "PROPERTY ID" and then rename "PROPERTYVALUE" to a more relevant thing, e.g. "PROPERTY DISTANCE FROM CBD" or whatever the number 34500 means. – Jeffrey Kemp Feb 21 '13 at 04:02

1 Answers1

1

if I have to add a new property, I have to change the schema

Is that actually a problem? Adding a column has gotten cheaper and more convenient in newer versions of Oracle.

But if you still need to make your system dynamic, in a sense that you don't have to execute DDL for new properties, the following simple EAV implementation would probably be a good start:

CREATE TABLE FOO (
    FOO_ID INT PRIMARY KEY
    -- Other fields...
);

CREATE TABLE FOO_PROPERTY (
    FOO_ID INT REFERENCES FOO (FOO_ID),
    NAME VARCHAR(50),
    VALUE VARCHAR(50) NOT NULL,
    CONSTRAINT FOO_PROPERTY_PK PRIMARY KEY (FOO_ID, NAME)
) ORGANIZATION INDEX;

Note ORGANIZATION INDEX: the whole table is just one big B-Tree, there is no table heap at all. Properties that belong to the same FOO_ID are stored physically close together, so retrieving all properties of the known FOO_ID will be cheap (but not as cheap as when all the properties were in the same row).

You might also want to consider whether it would be appropriate to:

  • Add more indexes in FOO_PROPERTY (e.g. for searching on property name or value). Just beware of the extra cost of secondary indexes in index-organized tables.
  • Switch the order of columns in the FOO_PROPERTY PK - if you predominantly search on property names and rarely retrieve all the properties of the given FOO_ID. This would also make the index compression feasible, since the leading edge of the index is now relatively wide string (as opposed to narrow integer).
  • Use a different type for VALUE (e.g. RAW, or even in-line BLOB/CLOB, which can have performance implications, but might also provide additional flexibility). Alternatively, you might even have a separate table for each possible value type, instead of stuffing everything in a string.
  • Separate property "declaration" to its own table. This table would have two keys: beside string NAME it would also have integer PROPERTY_ID which can then be used as a FK in FOO_PROPERTY instead of the NAME (saving some storage, at the price of more JOIN-ing).
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167