-2

Currently I try to solve the following problem: The document management software I´m working with can define masks for there documents and each mask has one or more indexfields (type:String). The system supports the following databases:

  1. Postgres 2. ORACLE 3. Microsoft SQl 4. DB2

Now I should do a research if it is possible to store the array of strings in just one field of the sql database so that each mask only takes up one line in the sql-table. One more requirement is that the array is searchable via sql.

The result should be that the sql-database isn´t as fragmented as it is now, cause every indexfield is one column in the mask definition and also in regard of performance the result should be equal (or if possible faster).

The current state is that I have working examples for postgres ( but only with Double[] so far ) and ORACLE ( via the ArrayDouble type ) with good results (no fragmentation and with performance improvements (>200%)). The examples are written in java ( standard jdbc connection ).

For DB2 I found some documentation about ordinary arrays (and two other types) but was not able to build a working example and for Microsoft SQL I was not able to find any hint that it has an array feature built in.

So my main question is if you guys have experience with this problem and can give me advice esspecially regarding DB2 and Microsoft SQL in how to tackle this problem. Also if you have an awesome solution for postgres and/or Oracle please share your knowledge with me. :)

Thanks in advance :) wish you all a enjoyable monday morning :) Greetings Pascal

LeBraveLittleToaster
  • 177
  • 1
  • 13
  • 2
    Storing an "array" of data in a single column generally means that you have a poor design. A single column in a single row should, normally, be representative of one thing. If you have a many-to-one or many-to-many relationship, you should be designing multiple tables to hold that, not a single column to hold an array/delimited string/etc. – Thom A Aug 06 '18 at 08:50
  • "[...] so that each mask only takes up one line in the sql-table" - why? – Mat Aug 06 '18 at 08:50
  • My problem is that I have no control over the design :/ – LeBraveLittleToaster Aug 06 '18 at 08:53
  • 1
    Then who does? They should be aware that the practice they are following is a poor design. I can only talk from a SQL Server point of view, however, storing data in an "array" (SQL Server doesn't have arrays, so you'd have to use a delimited string, XML, JSON, or something) will almost always have performance implications, some far more severe than others. If you can push the design in the right direction, you'll be in a far better position. – Thom A Aug 06 '18 at 09:00
  • Thanks for the advice, my problem is that I´m a second year student trying to get some real world experience so I belief what real developers tell me at my intership :/ in this scenario what do you would advice as a suitable design? Just need a well fundamented statement to convince them :/ – LeBraveLittleToaster Aug 06 '18 at 09:05
  • 1
    I got this article from a quick [Google](http://google.com), which shows (what I would call) the traditional method for managing a many-to-many relationship by using a composite key table: [How to Handle a Many-to-Many Relationship in Database Design](https://dzone.com/articles/how-to-handle-a-many-to-many-relationship-in-datab) – Thom A Aug 06 '18 at 09:18
  • Thanks a lot Larnu – LeBraveLittleToaster Aug 06 '18 at 09:25

1 Answers1

1

With Oracle, you can store arrays in a column using NESTED TABLEs (or VARRAYs):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TYPE String_Table IS TABLE OF VARCHAR2(100)
/

CREATE TABLE test (
  id     NUMBER(10,0),
  col1   VARCHAR2(10),
  array1 String_Table
) NESTED TABLE array1 STORE AS test__array1
/

INSERT INTO test ( id, col1, array1 )
  SELECT 1, 'Row1', String_Table( 'A', 'B', 'C' ) FROM DUAL UNION ALL
  SELECT 2, 'Row2', String_Table( 'C', 'D', 'E' ) FROM DUAL
/

Query 1: Then you can use collection operations such as: MEMBER OF to find items in a collection; and MULTISET operators like SUBMULTISET OF to find collections containing all items of another collection.

SELECT *
FROM   test
WHERE  'B' MEMBER OF array1
OR     String_Table( 'E', 'C' ) SUBMULTISET OF array1

Results:

| ID | COL1 | ARRAY1 |
|----|------|--------|
|  1 | Row1 |  A,B,C |
|  2 | Row2 |  C,D,E |

If you are using Java then you can pass Java arrays as bind parameters of a PreparedStatement or CallableStatement. Some examples of this are here and here.

MT0
  • 143,790
  • 11
  • 59
  • 117