Data design question

[spiral123]

Hey guys I could use some help.
I have a situation where i have 1- 100 fields of data coming in on a timed basis. Average is 10 at a time.
Should I do this:
id|field|value|timestamp
which means each time I recieve data I get 1-100 records,
or should I do this:
id|field 1 |Field 2| ......|Timestamp
in which case I get 1 record but an average of 90 empty fields.
so are a lot of small rows (4 fields) better than a much smaller amount of large rows (103 fields) that have a lot of empty fields?

Just making sure everyone understands what I'm ending up with.

any advice is appreciated.

 

[simian101]

If all the data is the same you should always go vertical.

You can always query the data and seperate it in to fields if neccessary.

But is sounds like it is all the same?

Simi

 

[RiverGuy]

Without knowing anything else, I would store them as rows instead of columns. I might break it down into two tables:

DataImportHeader
------------
ImportID INT IDENTITY PRIMARY KEY
DateTimeImported DATETIME
FieldCount INT

DataImportValues
------------
ImportID INT FK/PRIMARY KEY
FieldID INT/VARCHAR/Whatever PRIMARY KEY
FieldValue VARCHAR????

 

[spiral123]

data is sent by units away from the server each unit can have 1 - 100 logs sent every 5 - 15 mins.
So data can be any integer or float. And there will be alot of data. I want the most efficient way to store this data so it will be easily retrieveable and lowest overhead possible.
Riverguy, I really like your suggestion it would save storing date field in every record and if I wanted other data done the road I could add it in to the header table.
But would joing the two tables make retrieval slower?

 

[RiverGuy]

Joining one table shouldn't slow anything down much if your tables are properly indexed. Even if you stuck to the single-table design, you'll still need proper indexes for data retrieval.