Posted by othellomy on 12/15/06 05:34

Then maybe you could create a view for the second table.

Matik wrote:
> 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]