Need your assistance to in design Database:

Question

Need your assistance to in design Database:

Rakesh Kumar Gupta

SSC Journeyman

Points: 85
More actions
September 17, 2004 at 2:03 am

#112437

Need your assistance to in design Database:

Following is the present structure of a table:

column Name   DataType

Ch_Number     integer
DateTime        smalldatetime/integer (please suggest)
Value            Numeric

Ch_Number and DateTime will be defined as composite primary key.
Above table will be containing millions of records.
There are total 512 different channel numbers (Ch_Number). Logging data to this table will be time stamped with the interval of 1 min. So there may be maximum 512 records every min. data will be keep logged continuously for 30 days. There are very similar tables to store other data.

One typical record will like this

001    "17/09/2004 09:02" (or 170920040902)  50.5
002    "17/09/2004 09:02" (or 170920040902)  47.5
.
.
.
.
001    "17/09/2004 09:03" (or 170920040903)  49.0
002    "17/09/2004 09:03" (or 170920040903)  48.5

There is report which takes the data from this table. Query will be either based on ch_Number or for defined interval of DateTime

Typical format is

Ch_Number DateTime1        DateTime2...            DateTime 10 (Value for these column will be from come above table.)
                    Value            Value                        Value
0001            50.2                49.0                        .......
0002            47.5                48.5                        ......
.
.
.
.

My main concern are:

With millions of records will the query enough fast for such report since data are stored in rows will be displayed in columns as mentioned in above format Or we can 512 different columns with one datetime column.  In present case same datetime value will be repeatedly logged for all the different ch_numbers ( but not all the time 512 channels will be logged many time it may only 250 to 300 different Ch_Number will be logged). Please give your comments on such design of table. Please let me know if there is any doubt from my side if i am not explain my doubt and you need for information.

Thanks and Regards
Rakesh

rakesh

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Douglas De Ivey Old Hand Points: 396 More actions · Answer 1

Hi,

I generally stick to the 'fully normalised' solution if there is any doubt as it provides the most flexibility regarding how the data will be used. if you create a table with 512 columns, then what will you do when the 'users' want the erport in a different format.

I would stick to the 3 column table and add extra indexes etc to help the performance of the reports.

Incidently, on the same basis I would always store dates and times in datetime fields as that makes sure that they are valid and it simplifies the handling when you come to search and display them.

You would have to have a very slow server and / or a much bigger database before it would be worth messing about with 'special' fields.

The ch_number could be stored in a smallint.

From your information, it is clear the there cannot be more than 512 new records per minute. and they are very small, so the server should have no difficulty keeping up.

The reporting side however may be more of a challenge if you want to be able to a variety of reports covering different time periods in a reasonable time.

So if you create the table with a clustered index of

Ch_Number

DateTime

This will allow 'fast' access where the ch_number is specified as key.

If you also create a non clustered index of

DateTime

Ch_Number

Value (yes include Value)

This will give you a covering index for fast access using datetime as the key.

The extra overhead in maintaining the index should not be a problem because we only have 512 inserts per minute.

Next, I would create views to support the reports that you need and then see how it goes. If you need better report performance you could try changing the views to indexed views. This would improve report performance but slow down the inserts (probably still not a problem)

Hope this helps

Rakesh Kumar Gupta SSC Journeyman Points: 85 More actions · Answer 2

Thanks for your help. I will keep all your suggestion while designing database and come back to you when required.

Regards

Rakesh

rakesh