Re: design question

Chris Seidel 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!

"Best" depends on what you are going to do with it other than store it.

"Best" depends on whether you require it reconstituted as XML at some point in the future.

"Best" depends, to a lesser extent, on your version which you don't state (3 decimal places).

My preference for reasons that range from performance to reporting to storage to relational theory is to shred XML into its constituent elements storing each with its proper data type.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org