Database Design Advise

[myspiral]

I have an application that does the following.
A UDP packet comes into the server with say 5 pieces of data (5 varies widely) and the receive program creates an entry in a table we'll call 'packet'.The entry looks like:
[ProductID] [Timestamp] [PacketID] - [PacketID] is primary key
Then each piece of data is inserted into a table we'll call 'productresults' and looks like so:
[PacketID] [Value] [id] - [id] is primary key
so in this instance the packet record tells us the productid and timestamp for each of the 5 pieces of data (remember 5 can be 1 - 100 it varies by product)

Was this the best way to store this?

We can have hundreds of products, should I have 1 table for all results or should I have a seperate table for all resulting in hundreds of tables?

Any advice is highly appreciated.

 

[markros]

One table is always better, IMHO. You can always partition it somehow (though not clear yet how for your case).

 

[Emtucifor]

I would not use hundreds of tables.

It is really poor practice to have to create database objects every time a new business name is created (new product -> new table = bad).

In some narrow cases where databases are big enough and the process is automated, I can see individual tables containing only months, but only when used as part of a partitioned view and the data access is always made through the view. Then the scripts that handle rolling months in and out of the partitioned view can be dynamic SQL to the nth degree, but the whole system is protected from the nasty practice of naming tables or columns with instances of business names (rather than types of business names).

E.g., a column called February is terrible. So is a column called Customer123 (when it literally refers to customer 123).

 

[myspiral]

in your opinion then i am doing it right?

 

[markros]

I think using just two tables you have right now is OK.