Re: design question

On Sep 5, 11:03 am, "Chris Seidel" <csei..._at_arcor.de> wrote:
> 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!

No offence, but option A is not only an astonishingly terrible idea, but a well known astonishingly terrible idea. It's actually one of the classic astonishingly terrible ideas. For example, see http://oracle-wtf.blogspot.com/2006/02/eav-returns-concrete-elephant-approach.html

What if you want all rows containing both "Hello" and "Foo"? What if you want unique or foreign key constraints? It's a fact of life that unconstrained data gathers incorrect values as time goes on. Along with all the date-range and integer arithmetic queries already mentioned, that's a whole world of flexibility you are sacrificing in the name of flexibility. Even the queries that remain possible become complex and inefficient. My advice would always be to do the data modelling properly and design an interface such that the model is separate from the client application and presentation logic. Received on Sun Sep 07 2008 - 12:54:39 CDT