Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    DB 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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need a new table with a compound primary key -- date, link, app
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    5
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use something bigger than TINYINT, and i would use DECIMAL instead of FLOAT

    otherwise, yes, that design looks okay...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    5
    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)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •