On Fri, 24 Apr 2015 13:37:40 +0000
"Drago, William @ CSG - NARDA-MITEQ" <William.Drago at L-3com.com> wrote:
> I'm trying to avoid re-inventing the wheel. Is there a best or
> generally accept way to store arrays of complex numbers?
A table in First Normal Form has no repeating groups. That means no
row has an array of any kind. Arrays in the relational model are
represented in columns, one element per row.
A column whose type is nonrepeating is said to be "atomic", but that's
something of a convention. An atom is supposed to be indivisible, but
we can take substrings of string, parts of dates, and exponents of
floating point numbers. So nonrepeating datatypes aren't necessarily
atomic, exactly. They're just not repeating. ;-)
The question of your complex array then comes down to two apects: how
to represent the complex number, and how to represent the array. The
case for the array is simple: keep one complex number per row. The
case for the "divisible atomic" complex number depends on a choice: how
you want the DBMS to treat the components of the complex type.
The most general solution -- and therefore probably the best one -- is
to keep the real and complex component each in its own REAL column.
That lets you sort and select the complex numbers using SQLite's
built-in functions without limitation. For example, if we call those
components "a" and "b", you could say,
select * from T where "a" between 1.0 and 2.0
Such a table would be
create table complex_array
( name TEXT not null
, ordinal INTEGER not null
, real_part REAL not null
, imaginary REAL not null
, primary key( name, ordinal )
);
That's the textbook solution on a DBMS without user-defined types.
An alternative is to conceive of the complex type as a datatype, and
represent it in SQLite as BLOB or TEXT. That severely limits
SQLite's ability to compare and select the values, although that
limitation can be somewhat alleviated with user-defined functions e.g.,
select * from T where "C" = complex(1.0, -0.5)
If 1) you're not interested in letting the DBMS inspect the data, and
2) you have some convenient C function to losslessly convert your
complex type to a string or bit-string, then a single-column
representation might be more convenient.
--jkl
