Re: Q:Table designing issue?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 14 Nov 2000 21:27:30 -0000
Message-ID: <974233576.16676.1.nnrp-14.9e984b29@news.demon.co.uk>

With reference to the previous post,
you shouldn't use LONGs in Oracle 8,
these have been superseded by BLOBs
and CLOBs.

Alternatively, you don't seem to have an identifier for each element of the type, but how about:

create table assets (

    id                number primary key,
    flags            number,

    comment varchar2(256)
);

create table a_type (

    id number references assets,

    attribute1 char(1),
    attribute2 char(1),
    attribute3 char(1),
    attribute4 char(1)

);

create table b_type(

    id number reference assets,     rate1 number,
    rate2 number
)

create table c_type (

    id number references assets,
    attribute5har(1),

    attribute6char(1),
    attribute7char(1),
    attribute8char(1)

);

Many other details and options to consider and fill in. In particular there is no limit on the number of structs associated with each asset.

Alternatively, for an object approach:

create type a_row as object(

    attribute1 char(1),
    attribute2 char(1),
    attribute3 char(1),
    attribute4 char(1)

);

create type a_table as varray(26) of a_row;

similar code for b_row, b_table, c_row, c_table; then

create table assets (

    a_struct    a_table,
    b_struct    b_table,
    c_struct    c_table,
    flags        number,

   comment varchar(256)
);

• actually 'comment' is a reserved word in Oracle.

On the whole I prefer the relational approach to

the object-like approach, but for a small database
the object-like approach might be friendlier for
your front-end coders.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:                 Addison Wesley Longman
Book bound date:     8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i

Xuening Sun wrote in message ...


>Hi,

>

> I was given a flat file which contains metadata about multimedia objects

>(video, audio,

>image and text). The file structure is created in C using nested

>"struct". I was

>asked to create new tables in Oracle database and to move the metadata

>from the

>flat file into the tables. So applications can retrieve the meta-data

>directly from

>a database. The structure within the flat file looks like

>

>struct {

>

>   struct {

>     unsigned char  attribute1;

>     unsigned char  attribute2;

>     unsigned char  attribute3;

>     unsigned char  attribute4;

>    }  A[26];

>

>   struct {

>     unsigned long  rate1;

>     unsigned long  rate2;

>    }  B[10];

>

>   struct {

>     unsigned char  attribute5;

>     unsigned char  attribute6;

>     unsigned char  attribute7;

>     unsigned char  attribute8;

>    }  C[20];

>

>   unsigned char  flag;

>   char           comments[256];

>  } asset;

>

>My question is: how do I convert the above file structures to a table(s)?

>

>

>Many thanks

>

>

>Xuening Sun

>

>

>

>--

Received on Tue Nov 14 2000 - 15:27:30 CST