I have a need to keep a bunch of data regarding some wan links, and I don't know how to design my database for it.
Here's the data I need to store.

I have about 35 different links, and about 50 different application volumes that need to be stored in this database, for every hour of the day.
Right now the way I have it setup doesn't seem too good, but i can't figure out any other way.
I currently have 1 different table for each link, and every application as a column in these tables.
So for example:
Table Link 1
Date App1 App2 App3 App4
Jan 1 01:00 54613 351546 6848435 6847684
Jan 1 02:00 668468 6846433 646464 6546846

Table Link 2
Date App1 App2 App3 App4
Jan1 01:00 6846 6844354 6846434 38463434
Jan1 02:00 648458 3848646 6846 684684

etc.

The primary key on each table is the date.
I'm only storing about 7 applications right now, but I need to be able to increase this to about 50, so it's gonna make for huge queries for reporting.
The thing is I need to be able to report on total application volumes across every link.
So that means right now i'm doing a Sum(Link1.App1) + Sum(Link2.App1).... which I have to do 35 times, and then repeat again for the next application. And most reports have a lot of applications required so it makes for queries that are 20 pages long...

Is there any design solution that would make this more efficient?
Thanks!

you need a new table with a compound primary key -- date, link, app

Yeah...
I was thinking of doing something like this...

tb_Application
AppID tinyInt
AppName nvarchar

tb_Link
LinkID tinyInt
LinkName nvarchar

tb_Ref_App_Link
AppID tinyInt
LinkID tinyInt
Date smalldatetime
Volume float

I think this would give me a good design?

i would use something bigger than TINYINT, and i would use DECIMAL instead of FLOAT

otherwise, yes, that design looks okay...

Ok.
Appreciate it.

One more thing.
What if each application can belong to an application group...
like
say the application "Citrix" belongs to the app group "ThinClient".


I was thinking of creating an extra table called
tb_AppGroup
AppGroupID Int (pk)
AppGraoupName nvarchar

and modifying my tb_Application to
AppID Int (pk)
AppName nvarchar
AppGroupID Int (fk)

that works