Posted by Matik on 12/14/06 13:46

Hi to everyone,

My problem is, that I'm not so quite sure, which way should I go.

The user is inputing by second part application a long string (let's
say 128 characters), which are separated by semiclon.
Example:

A20;BU;AC40;MA50;E;E;IC;GREEN

Now: each from this position, is already defined in any other table, as
a separate record. These are the keys lets say. It means, a have some
properities for A20, BU, aso.

Because this long inputed string, is a property of device (whih also
has a lot of different properities) I could do two different ways of
storing data:

1. By writing, in SP, just encapsulate each of the position separated
by semicolon, and write into a different table with index of device,
and the position in long stirng nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3
123 MZD12 XX77 .... any comment text
124 MZD13 XY55 ... any other comment

String data Table
fk_deviceId position value
123 1 A20
123 2 BU
123 3 AC40
.....
123 8 GREEN

The device table, contains also a pointer (position), which might
change, to "hglight" specified position.

Then, I can very easly find all necessary data. The problem is, I need
to move the device record data (from other table) very often into other
history table (by each update). That will mean, that I also need to
move all these records from 1 -8 for example to a separate history
table, holding the index for a history device dataset. This is a little
inconvinience in this, and in my opinion, it will use to much storage
data, and by programming, I need always to shift this properities into
history table, whith indexes to a history table of other properities.

2. Table will be build nearly in this way:

Major device data table
ID AnyData1 AnyData2 ... AnyData3 stringProperty pointer
123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3
124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2

By writng into device table, there will be just a additional field for
this string, and I will have a function, which according to specified
pointer, will get me the string part on the fly, while I need it.
This will not require the other table, and will reduce the amout of
data, not a lot ... but always.
This solution, has a inconvinance, that it will be not so fast doing a
search over the part of this strings, while there will be no real index
on this.
If I woould like to search all devices, by which the curent pointer
value is equal GREEN, then I need to use function for getting the
value, and this one will be not indexed, means, by a lot amount of
data, might be slow.

I would like to know Your opinion about booth solutions.
Also, if you might point me the other problems with any of this
solution, I might not have noticed.

With Best Regards

Matik

[Back to original message]