Posted by Erland Sommarskog on 12/14/06 23:15

Matik (marzec@sauron.xo.pl) writes:
> 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.

This is the normal design in this situation.

> 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

This design violates a basic principle in relational design: no repeating
groups.

Every rule is made to break, and I have occasionally put repeating groups in
the database I maintain, but this is a clearcut case: don't even think
about it. This sort of data is very difficult to work with in a
relational database, simply because it's not meant that you should
store data in this way.

> 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,

With a sub-table you need to repeat the ID. There will also be a cost
of two bytes for the length of each column. There is also the cost for
the field number, but since you don't have any semi-colon, this is a
net cost of one byte. There is also some overhead for each row. But
all and all, I would say that the overhead is about neglible.

> and by programming, I need always to shift this properities into
> history table, whith indexes to a history table of other properities.

Don't really know what you mean here.

For completeness sake I should say that there is a third alternative,
and that is one table, but eight columns. This could also be considered
a repeating group. Then again, if the different fields represents
different attributes, it isn't really an repetition. This solution
is better my opinion than a seprated list, but the pointer you talk
about may be more difficult to implement.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]