Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Db design any pointers?

    I have a design issue that I would like some advice regarding, any help and pointers would be greatly appreciated, this is in a very early stage as I am sure you will see, I'm a C# developer not a dba and whilst I have some database knowledge it is not my primary skillset.

    I have a table of phonecall information, the records include a numberdialled column. I have been asked to create an exlusions table, these numbers will essentially constitute an allowed numbers list.

    For each record in the calls table I need to query the db and get information regarding billing periods, individuals etc indicating whether the calls were Company calls (a match was found in the exclusions table) or personal (it was not found).

    I have created a view that returns the information as follows;

    SELECT
    dbo.PhoneCalls.Id,
    dbo.PhoneCalls.UserName,
    dbo.PhoneCalls.OwnNumber,
    dbo.PhoneCalls.CallDateTime,
    dbo.PhoneCalls.NumberDialled,
    dbo.PhoneCalls.Area,
    dbo.PhoneCalls.Duration,
    dbo.PhoneCalls.Cost,
    dbo.BillingPeriod.Name AS BillingPeriod,
    dbo.BillingPeriod.StartDate,
    dbo.BillingPeriod.EndDate,
    CASE WHEN dbo.PhoneCalls.NumberDialled IN
    (SELECT
    dbo.ExclusionNumbers.PhoneNumber
    FROM
    dbo.ExclusionNumbers
    WHERE
    dbo.ExclusionNumbers.CreatedDate < dbo.BillingPeriod.EndDate
    )
    THEN 'Company' ELSE 'Personal' END AS CallType
    FROM
    dbo.PhoneCalls
    CROSS JOIN
    dbo.BillingPeriod
    WHERE
    (dbo.PhoneCalls.CallDateTime BETWEEN dbo.BillingPeriod.StartDate AND dbo.BillingPeriod.EndDate)


    Table information
    -----------------

    CREATE TABLE PhoneCalls(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [OwnNumber] [nvarchar](20) NOT NULL,
    [CallDateTime] [datetime] NOT NULL,
    [NumberDialled] [nvarchar](20) NOT NULL,
    [Area] [nvarchar](50) NOT NULL,
    [Duration] [nvarchar](20) NOT NULL,
    [Cost] [money] NOT NULL,
    CONSTRAINT [PK_PhoneCalls] PRIMARY KEY CLUSTERED

    CREATE TABLE ExclusionNumbers(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ExclusionSourceId] [tinyint] NOT NULL,
    [PhoneNumber] [nvarchar](20) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_ExclusionNumbers] PRIMARY KEY CLUSTERED

    CREATE TABLE BillingPeriod(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NOT NULL,
    CONSTRAINT [PK_BillingPeriod] PRIMARY KEY CLUSTERED

    The problem I have is that the exclusions table is going to get quite large, probably around 25000 records, the call information table will probably be taking about 50000-100000 records per month too, so I am going to be scanning 25000 records with that ugly nested statement. At the moment for testing I have two exclusion numbers and around 6000 call records, it is fine now but this just looks dirty to me, surely I can do this a better way. Once again many many thanks for any pointers,

    kind regards,
    John.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT c.Id
         , c.UserName
         , c.OwnNumber
         , c.CallDateTime
         , c.NumberDialled
         , c.Area
         , c.Duration
         , c.Cost
         , b.Name AS BillingPeriod
         , b.StartDate
         , b.EndDate
         , CASE WHEN e.PhoneNumber IS NOT NULL
                THEN 'Company' 
                ELSE 'Personal' END AS CallType
      FROM dbo.PhoneCalls AS c
    INNER
      JOIN dbo.BillingPeriod AS b
        ON c.CallDateTime BETWEEN b.StartDate AND b.EndDate
    LEFT OUTER
      JOIN dbo.ExclusionNumbers.PhoneNumber AS e
        ON e.PhoneNumber = c.NumberDialled
       AND e.CreatedDate < b.EndDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    2

    Talking

    LEFT OUTER JOIN [bangs head with palm of his hand], very much appreciated.

    kind regards,
    John.

Posting Permissions

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