Database design question

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 to supply more information. It sounds to me like you might need an APPLICATION table to describe each applaction and a LINK table to describe each specific link -- but without addtional information might change that. It also sounds like you need at least one more table to record your time-sensitive data; however, I don't clearly see this at the moment.

Like I said: More information is needed.

|||

Ok.

Basically, let's say I have 35 different wan links going across my network.

Going across those links are a bunch of different applications. Let's say there's 50 of them.

I have a system that records the volume generated by each application over everyone of those 35 links, every hour.

Now I need to be able to store this data long term.

So the only thing being stored really is the volume for each application.

Right now it's setup this way:

TableLink1

Date Citrix FTP Telnet HTTP ..................etc

Jan 1 01:00 839203 923849 239487 83823

The date/time is the primary key as there will never be two entries for the same date/time.

The numbers being stored under the application columns are bytes.

I will need to be able to report on a few different things. For example, I will have to be able to produce the Top 10 applications for a given time period.

So that will mean i'll have to add up every application for every link and determine which 10 volumes are highest. With 35 different tables right now, my query

is extremely long and I don't know if it's supposed to be like that.

There's also a requirement for reports on a specific application across all links. So for example, I'll need to show the total volume used by FTP across

the 35 links for a given time period.

With the design that i'm using right now, it's possible, but it's a long query which looks like:

SELECT SUM(TableLink1.Citrix) + SUM(TableLink2.Citrix) + SUM(TableLink3.Citrix) .....etc AS Citrix

I hope this is enough info...if not tell me what you require. I'm pretty sure my design is ok, but just want to confirm if there's no better way.

I was also considering a different design that would look like this:

ApplicationCitrix

Date Link1 Link2 Link3 ...... Link35

Jan 1 01:00 23487234 234234 23487 ...... 3248324

Basically instead of using a different table for each link, i'd use a different table for each application...but I think it comes back to the same thing.

|||

Probably create application table something like this:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

Link table something like this:

tb_LINK

LinkID tinyint identity(1,1)

LinkValue bigint

and finally a cross-reference table:

xRef_App_Link

ApplicationID (fk)

LinkID (fk)

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

Umm...i'm not sure i'm quite following...

If you can explain a little more maybe.

I don't get how the tb_LINK table would work...

How can I store a LinkValue without associating it to a specific application...

The way i'm thinking is maybe this:

keep the same tb_APPLICATION

then the tb_LINK:

LinkID tinyint identity(1,)

LinkName nvarchar(255)

And then the cross reference table:

xRef_App_Link

ApplicationID (fk) (pk)

LinkID (fk) (pk)

Date (pk)

Volume

Would this work?

I didn't quite get the part where you wrote:

LinkStartDate smalldatetime

LinkEndDate smalldatetime

|||

I think you want the app table:

tb_APPLICATION

ApplicationID tinyint identity(1,1)

ApplicationName nvarchar(255)

I am not sure if you need a LINK table or whether all you need is a VOLUME table that contains something like

Volume

RowId identity

LinkId

ApplicationId

LinkVolume

LinkStartTime

LinkEndTime

|||

Ok,

I think I will do it this way instead of having 35 tables lol...

Thanks a lot for the help.

|||I did not mean my response as "the answer" and I think it is pre-mature to mark it as such. I was hoping to get other opinions. Other ideas?