Help with multiple values in database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Help with multiple values in database

  1. #1
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    *** I have posted the same message in Oracle Development forum.
    I am developing an application in pro*C and Oracle.
    I want to create a table that has a multi-valued column, meaning can hold more than one value.
    My problem is this:
    1) I thought about using a varchar2 type and contcatenating values through pro*C, but when i perform a select clause, i will have to use like %someting% and that will cause a full table scan.
    2) I thought about using object type, but i know oracle holds the object as lob type and i cant query the lob type for its contents.

    What do i do ???

    Thanx
    Rotem.


  2. #2
    Join Date
    Aug 2000
    Posts
    462
    nested table or separate that data into a child table and create a primary key - foreign key relationship between the original table and the new child table.

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    I am getting a String (char*) object and inside the string are the multiple values concatenated with a backslash
    i.e. VALUE1\VALUE2\VALUE3

    I don't know which data i will receive and i check the data by going over the string and extracting the concatenated values.

    How can i save them in the database table ???

  4. #4
    Join Date
    Nov 2000
    Posts
    212
    whatever you use (nested tables or varchar22 or long), consider the following portion of relational theory:

    Table column should have atomic values. Classic example of deciding on it is date datatype value: to store it as 3 columns (year, month, day) or as one value? In general, storing as 3 column value is more flexible for quering.

    However in your case one may consider using column1..colunmN, but such approach leads to really ugly denormalization (1'st normal form not satisfied).

    So it is strongly advised to have a separate table and to store every VALUEi into separate record.

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    1. parse string, putting each string segment into a variable
    2. insert/update child and parent rows with appropriate data

    Such as:

    create table parent_table(id number(10) primary key);

    create table child_table(id number(10) references parent_table(id), stringdata varchar2(50));

    suppose you have data to insert about one specific object, which includes a number to uniquely identify the object, and multiple string values as some data about the object.

    1. read the object id and the entire concatenated string
    2. parse string into individual variables
    3. check parent_table to avoid attempting to insert duplicate row for this object
    4. insert one parent_table record, if necessary
    5. insert one row in child table for each of those parsed string values, using the ID from the parent table as the ID in the child table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width