Re: design question

On Sep 5, 6: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!

A relational database is best used to store data in a relational format with attributes stored as using the appropriate data type so design B is better because you are storing each attribute in its own designated column of the correct database data type, that is, a numeric value in a number data type, a date value in a date data type, etc ... rather than storing everything as character as the first design would require.

Storing data this way will make the data available via other means/ methods than just the java application layer with its object building and handling logic.

IMHO -- Mark D Powell -- Received on Fri Sep 05 2008 - 08:25:57 CDT