design question

Hi,

I need to store java objects in my oracle database. These objects have serveral fields, e.g.

Object1: String f1; String f2; Date f3
Object1: String f1; BigDecimal f2; Boolean f3

e.g.
Object1("Hello", "World", 2008-09-05)
Object2("Foo", 100.20, true)

The database shall scale up at least to 100 million objects.

How to map this to the database?

A)

create table ObjectProperty (ID numeric(10), ObjectID numeric(10), Fieldname varchar2(50), Fieldvalue varchar2(4000));

So each object is stored into multiple rows in this table, one row per field.
Each value is converted to varchar.

1, 100, Hello
2, 100, World
3, 100, 2008-09-05
4, 101, Foo
5, 101, 100.20
6, 101, 1

B)

create table Object(ObjectID numeric(10), o1_f1 varchar(4000), o1_f2 varchar(4000), o1_f3 date, o2_f1 varchar(4000), o2_f2 numeric(30,2), o2_f3 numeric(1));

1, Hello, World, 2008-09-05, null, null, null 2, null, null, null, Foo, 100.20, 1

So each object is store in a single row. Values are store in there native format.

This could be optimized that Object1 and Object2 share fields of the same type.

What design is better A oder B and why?

Thank you! Received on Fri Sep 05 2008 - 05:03:09 CDT