Showing posts with label design. Show all posts
Showing posts with label design. Show all posts

Monday, March 26, 2012

optimized name-address-zipcode-region design

Whether thin-client with round-trip cycles to the db or a rich-client
loading look-up data into memory on application startup, what is a solid
optimized database design for customer location search functionality. USPS
zip data has about 75K records.
Any articles on this? Thanks. -Greg
**** ** Application supplied data *******
1. First Name (PrimaryContactID)
2. Last Name
3. Client Name
4. Address (AddressID)
****** Vendor supplied data
zipinfo.com,/www.zip-codes.com,www.buyzips.com,etc. *******
5. City (CityID)
6. Region (State,Province, etc) (RegionID)
7. Postal Code. (PostalCodeID)
8. Area Code (AreaCodeID)
an optimized search that would return all customers (within a second) for
any of the following:
1. a given zipcode/postalcode
2. a given State/Region,
3. a given City
4. a given Area Code
5. a given match on name or substring of address."hazz" <hazz@.sonic_net> wrote in
news:ejBkMi7bGHA.3908@.TK2MSFTNGP02.phx.gbl:

> Whether thin-client with round-trip cycles to the db or a rich-client
> loading look-up data into memory on application startup, what is a
> solid optimized database design for customer location search
> functionality. USPS zip data has about 75K records.
> Any articles on this? Thanks. -Greg
> **** ** Application supplied data *******
> 1. First Name (PrimaryContactID)
> 2. Last Name
> 3. Client Name
> 4. Address (AddressID)
> ****** Vendor supplied data
> zipinfo.com,/www.zip-codes.com,www.buyzips.com,etc. *******
> 5. City (CityID)
> 6. Region (State,Province, etc) (RegionID)
> 7. Postal Code. (PostalCodeID)
> 8. Area Code (AreaCodeID)
> an optimized search that would return all customers (within a second)
> for any of the following:
> 1. a given zipcode/postalcode
> 2. a given State/Region,
> 3. a given City
> 4. a given Area Code
> 5. a given match on name or substring of address.
>
>
Here is a link to an article I read this w on ASP FAQ
How do I create a store locator feature?
http://www.aspfaq.com/show.asp?id=2527
- Jose|||Thank you for that link Jose. That is not exactly what I was after but that
is interesting. The vendor db's do have latitude and longitude so this is a
good example of how that info is useful. -Greg
"Jose Hernandez" <acmp_dev@.yahoo.com> wrote in message
news:Xns97B98A2952D76acmpdevyahoocom@.207
.46.248.16...
> "hazz" <hazz@.sonic_net> wrote in
> news:ejBkMi7bGHA.3908@.TK2MSFTNGP02.phx.gbl:
>
> Here is a link to an article I read this w on ASP FAQ
> How do I create a store locator feature?
> http://www.aspfaq.com/show.asp?id=2527
>
> - Jose|||You can also get the postal code database from several vendors such as
http://www.zipcodeworld.com.

Tuesday, March 20, 2012

Optimization

We're building a company wide network monitoring system
in Java, and need some advice on the database design and
tuning.

The application will need to concurrently INSERT,
DELETE, and SELECT from our EVENT table as efficiently as
possible. We plan to implement an INSERT thread, a DELETE
thread, and a SELECT thread within our Java program.

The EVENT table will have several hundred million records
in it at any given time. We will prune, using DELETE, about
every five seconds to keep the active record set down to
a user controlled size. And one of the three queries will
be executed about every twenty seconds. Finally, we'll
INSERT as fast as we can in the INSERT thread.

Being new to MSSQL, we need advice on

1) Server Tuning - Memory allocations, etc.
2) Table Tuning - Field types
3) Index Tuning - Are the indexes right
4) Query Tuning - Hints, etc.
5) Process Tuning - Better ways to INSERT and DELETE, etc.

Thanks, in advance, for any suggestions you can make :-)

The table is

// CREATE TABLE EVENT (
// ID INT PRIMARY KEY NOT NULL,
// IPSOURCE INT NOT NULL,
// IPDEST INT NOT NULL,
// UNIXTIME BIGINT NOT NULL,
// TYPE TINYINT NOT NULL,
// DEVICEID SMALLINT NOT NULL,
// PROTOCOL TINYINT NOT NULL
// )
//
// CREATE INDEX INDEX_SRC_DEST_TYPE
// ON EVENT (
// IPSOURCE,IPDEST,TYPE
// )

The SELECTS are

private static String QueryString1 =
"SELECT ID,IPSOURCE,IPDEST,TYPE "+
"FROM EVENT "+
"WHERE ID >= ? "+
" AND ID <= ?";

private static String QueryString2 =
"SELECT COUNT(*),IPSOURCE "+
"FROM EVENT "+
"GROUP BY IPSOURCE "+
"ORDER BY 1 DESC";

private static String QueryString3 =
"SELECT COUNT(*),IPDEST "+
"FROM EVENT "+
"WHERE IPSOURCE = ? "+
" AND TYPE = ? "+
"GROUP BY IPDEST "+
"ORDER BY 1 DESC";

The DELETE is

private static String DeleteIDString =
"DELETE FROM EVENT "+
"WHERE ID < ?";Hi,

Refer www.SQL-Server-Performance.com web site for SQL Server Performance issues.
Almost all yours queries id answered in perfect articles and example wise.

Thanks, Amit

pwhittington@.nitrodata.com (rocky) wrote in message news:<3961374c.0307041440.4ebb73ef@.posting.google.com>...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";|||Some mixed advice:

1) Make sure that you have plenty of memory in the server. Query2 will
always generate a full index scan, and you need to be able to fit all of the
data for that index in memory to avoid excessive disk I/O. 100 million rows
* 13 bytes = 1.3 GB of memory. I would suggest having at least 2GB memory in
the server.

2) Make sure you have a disk subsystem with good performance for both
reading and writing. Use several striped disks for the data (not RAID5), and
a separate disk for log. Use a disk controller with a battery-backed write
cache. The disk performance is important even if you have plenty of memory,
but it is absolutely vital if you can not fit all the data in memory.

3) Add a NOLOCK hint to all SELECT queries. Otherwise, all inserts will be
blocked while executing the SELECTs.

4) Change the index to IPSOURCE,TYPE,IPDEST to optimize Query3

5) Make sure that you batch inserts. Have the INSERT thread generate batches
of inserts and send them all to the server in a single batch as a single
transaction. Having a transaction for each inserted row will kill
performance unless you have a really good disk controller.

/SG

"rocky" <pwhittington@.nitrodata.com> wrote in message
news:3961374c.0307041440.4ebb73ef@.posting.google.c om...
> We're building a company wide network monitoring system
> in Java, and need some advice on the database design and
> tuning.
> The application will need to concurrently INSERT,
> DELETE, and SELECT from our EVENT table as efficiently as
> possible. We plan to implement an INSERT thread, a DELETE
> thread, and a SELECT thread within our Java program.
> The EVENT table will have several hundred million records
> in it at any given time. We will prune, using DELETE, about
> every five seconds to keep the active record set down to
> a user controlled size. And one of the three queries will
> be executed about every twenty seconds. Finally, we'll
> INSERT as fast as we can in the INSERT thread.
> Being new to MSSQL, we need advice on
> 1) Server Tuning - Memory allocations, etc.
> 2) Table Tuning - Field types
> 3) Index Tuning - Are the indexes right
> 4) Query Tuning - Hints, etc.
> 5) Process Tuning - Better ways to INSERT and DELETE, etc.
> Thanks, in advance, for any suggestions you can make :-)
>
> The table is
> // CREATE TABLE EVENT (
> // ID INT PRIMARY KEY NOT NULL,
> // IPSOURCE INT NOT NULL,
> // IPDEST INT NOT NULL,
> // UNIXTIME BIGINT NOT NULL,
> // TYPE TINYINT NOT NULL,
> // DEVICEID SMALLINT NOT NULL,
> // PROTOCOL TINYINT NOT NULL
> // )
> //
> // CREATE INDEX INDEX_SRC_DEST_TYPE
> // ON EVENT (
> // IPSOURCE,IPDEST,TYPE
> // )
> The SELECTS are
> private static String QueryString1 =
> "SELECT ID,IPSOURCE,IPDEST,TYPE "+
> "FROM EVENT "+
> "WHERE ID >= ? "+
> " AND ID <= ?";
> private static String QueryString2 =
> "SELECT COUNT(*),IPSOURCE "+
> "FROM EVENT "+
> "GROUP BY IPSOURCE "+
> "ORDER BY 1 DESC";
> private static String QueryString3 =
> "SELECT COUNT(*),IPDEST "+
> "FROM EVENT "+
> "WHERE IPSOURCE = ? "+
> " AND TYPE = ? "+
> "GROUP BY IPDEST "+
> "ORDER BY 1 DESC";
> The DELETE is
> private static String DeleteIDString =
> "DELETE FROM EVENT "+
> "WHERE ID < ?";

Monday, March 12, 2012

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
Thx
Database design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>
|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
David Portas
SQL Server MVP
--

optimal database design

What is more efficient for a database design - a lot of tables with only a
few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not
sure whether to have a few very large tables (over 100,000 rows) or start a
new table for each user which would result in approx 1500 tables most of
which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
ThxDatabase design is based on the analysis of data entities of really world,
rarely on the amount/quantity of an entity. Usually an entity is translated
into a table in database, say, you have a database with Customers table,
Orders table, Order Details table... You do not create many customer tables
just because of too many customers' records, it is not optimal, it is simply
wrong. BTW, a table with a million rows may not be as that big as you
thought, unless the table has a lot columns and the columns' size is big.
For SQL Server, there is not problem to handle a database with a few
million-row tables with regular columns (say, a couple of dozen columns,
mostly numbers and short texts)
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
not
> sure whether to have a few very large tables (over 100,000 rows) or start
a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||100K rows is not a lot by any means in Sql Server. Trying to manage a
different table for each use sounds like a nightmare that you should wake up
from as soon as possible.
--
Andrew J. Kelly SQL MVP
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:D86EC2E3-A808-4809-AEF6-61F3618E0EEB@.microsoft.com...
> What is more efficient for a database design - a lot of tables with only a
> few records or a few tables with lots of records.
> I'm starting a new site and each user will have numerous records but I'm
> not
> sure whether to have a few very large tables (over 100,000 rows) or start
> a
> new table for each user which would result in approx 1500 tables most of
> which would be the same table design with different rows.
> I'm using SQL2000.
> I guess this is quite a basic question, but I'm a bit unsure.
> Any references anyone could point me too as well.
> Thx
>|||A new table for each user doesn't sound like a very sensible design.
Each table should represent a single entity - that normally means one
table for all things that have a common set of attributes (columns).
100,000 rows is very small in SQL Server terms (to most people anyway)
but whatever the size, start with a normalized logical design - then
think about performance considerations if performance evaluations
demonstrate you may have problems.
--
David Portas
SQL Server MVP
--

optimal database design

What is more efficient for a database design - a lot of tables with only a few records or a few tables with lots of records.

I'm starting a new site and each user will have numerous records but I'm not sure whether to have a few very large tables (over 100,000 rows) or start a new table for each user which would result in approx 1500 tables most of which would be the same table design with different rows.

I'm using SQL2000.

I guess this is quite a basic question, but I'm a bit unsure.

Any references anyone could point me too as well.

ThxThe number of rows is dependent upon a lot of things. The number of COLUMNS per row is more likely indicitive of good design. I would never create a new table for each user. That is why there are indexes.

This is an excellent book on Database Design:

Designing Effective Database Systems
By Rebecca M. Riordan.
Published by Addison Wesley Professional.

http://www.awprofessional.com/title/0321290933|||I agree with Douglas. From a reporting and maintenance standpoint, fewer tables will be much better. Implementing a different set of tables for each user will lead to many, many problems down the road.|||I had the same doubts and I was told to read about Database Normalization. It could help you too

Friday, March 9, 2012

Opinion Requested: Multi-Table Design

I have about a 35000 record table. There are about 14 entries in this table that relate to "sections". Each of these sections can have up to 20 values. This lends itself to a design like:


big_table [one-to-one] big_table_has_sections [many-to-many] sections

or, more concretely:


BuildingConstructionType
relates to
ConstructionHasTypes
relates to
Types

Where BuildingConstructionType is one of 14 fields in the 35000 record "big" table, ConstructionHasTypes is the one-to-one intermediary relation that relates many-to-many with Types (the sections).

Unfortunately, with 35000 records, this big_table_has_sections seems like it might bloat. Is this a good solution?In order to facilitate what I think you are trying to accomplish, I don't know of any better way. Presumably the cross reference table is smallish in nature (i.e. composed of 2-3 key fields) and therefore should be indexed for quicked retrieval.

The obvious difficulty lies in how to retrieve the data. Your concerns for bloat are well founded as you will be using joins to return the appropriate dataset. If you user the query analyzer you will see that even simple joins between 2 tables using indexed keys are typically the most expensive (time and resource) operation in getting the data back. The reason for this is that sql does a table scan each and every time!

For this reason when dealing with tables of any sgnificant size I always use a stored procedure that employs one or more temporary tables which are populated using the appropriate where clause(s). Then I perfrom any joins using the temporary tables previously generated. That way the table scan will only go through the 100 or whatever number of BuildingConstructionType records in the temporary table instead of all 35000.

Granted, doing this takes a little more time and effort but my clients have tables containing 2.5 -3 million records with joins up the kazoo and I can still kick out result set in under a second.

Hope this is helpful and feel free to ask for any additional assistance if required.

Cheers

Gary|||Thanks... that's what I feared. It would be great if I could represent a vector effectively, but that doesn't appear to be consistent with the declarative nature of SQL.

Opinion about design needed (splitting string data)

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

MatikMatik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Matik (marzec@.sauron.xo.pl) writes:


Quote:

Originally Posted by

Quote:

Originally Posted by

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


Probably something along the lines of: (oversimplified for brevity)

insert into FooHistory select * from CurrentFoo
delete from CurrentFoo|||Then maybe you could create a view for the second table.

Matik wrote:

Quote:

Originally Posted by

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

|||First of all, thank you for your reply!

Now, some additional explenations maybe:

That was just an example, with 8 positions separated by semicolon as a
one property. The problem is, there number of this is various. That's
why, I couldyn't solve issue with fix number of column.

With shifting data into history, I've ment, that by each change of data
in primary table, whole record should be copied to the history table
(nearly same construction as primary table).
This is than an issue with the second table, storing semicolon
separated field in one column (splitted) in different table. This need
to be shifted then also, to a second historical table.

Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.

This semicolon devided string, as example was shown pretty simmilar,
but it can be also various:

A10;B13;c20;bubu;lala;GREEN;RED
A13;BUBU;GREEN;YELLOW;mama
C25;YELLOW
BLUE;pleple;B13
aso.

The pointer I was talking about, is just a index, to which position in
this semicolon devided string, is curently activated.

Best regards

Matik|||Matik wrote:

Quote:

Originally Posted by

The problem is, I'm afraid a little of performance,


This has "premature optimization" written all over it. Build the
database cleanly first; then, if you /actually/ have performance
issues, then consider how to improve it (but breaking 1NF with "a;b;c"
type columns should still be a last resort).|||Matik (marzec@.sauron.xo.pl) writes:

Quote:

Originally Posted by

With shifting data into history, I've ment, that by each change of data
in primary table, whole record should be copied to the history table
(nearly same construction as primary table).
This is than an issue with the second table, storing semicolon
separated field in one column (splitted) in different table. This need
to be shifted then also, to a second historical table.


I'm not sure that I see the problem. With a regular design, you would
have two tables for current data, and two tables for historical data.

Quote:

Originally Posted by

Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)


Like Ed said, get the design right first, and do performance tuning
when everything else is working. But some basic ideas for performance
are good when designing for performance. For instance no repeating
groups (i.e. semicolon-separated lists.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Matik wrote:

Quote:

Originally Posted by

>
Of course, I could ommit using 'working' table, and have only history,
with inserts, and having a primary table containing a pointer to last -
newest record as my primary table, to get the newest record.
The problem is, I'm afraid a little of performance, sice there is all
other actions done on the primary table (select, searches aso.)
Having a big historical table, I will still need to get countinous
joins, to get the newest record, and even having a good indexing and
relation set up, it might be slow while table can be big.
>


The way to optimise is with good indexes and good query design. You say
"it might be slow" so obviously you haven't reached that stage yet. On
the other hand you know for sure that a redundant copy of the data will
have an additional performance cost, both for updates and queries.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--