Solved

DB Design: am I right on this design?

Posted on 2007-03-25
10
244 Views
Last Modified: 2010-03-19
Hi experts, I have a database design question which I would like you to help me with.  I already know what I want to do, I just need your approval. :)

I have three existing tables:  [PhoneCalls], [Demos] and [Contacts].  When a telemarketer makes a phone call, it is to a contact so the FK from contacts gets added to the phone call which was made.  However, I also need to log if the telemarketer sets a demo.  Since 1 demo will only be set out of every 10 to 30 calls, I don't want to add an integer field FK to [PhoneCalls] from [Demos].PK_Demo_ID - this will just take up unnecessary space when a demo is NOT set.  

To fix this problem, I am proposing to add a table between [Demos] and [PhoneCalls] called [PhoneCallsDemos] which will act as a 1-to-many table.  In doing this, if I wanted to run a report to find all of the calls which resulted in a demo, I could simply do a join from the [phonecalls] table to the [PhoneCallsDemos] table...  Likewise, if I wanted to find all fo the demos which resulted from a phone call, I could do the same thing from the [Demos] table.

What do you think?  It seems as if it is the best way, I just need a confirmation from the real experts...  Oh, and on the same line... When the telemarketer adds a referral we need to be able to pull reports and see things like how often the telemarketer gets a referral from the call.  Should I take the same approach to this as well?

---------- CURRENT TABLES -------------------
[Demos]
 - PK_Demo_ID (int)
 - DemoStart (smalldatetime)
 - DemoEnd (smalldatetime)
 - FK_Result_ID (tinyint)
 - more fields...

[Contacts]
 - PK_Contact_ID (int)
 - FirstName (varchar 15)
 - LastName (varchar 15)
 - FK_ReferredBy_ID (FK to PK_Contact_ID - 0 for not referred)

[PhoneCalls] - Where the call is stored when the telemarketer makes one
 - PK_Call_ID (int)
 - TimeCalled (smalldatetime)
 - EndTime (smalldatetime)

* Note:  I have put table names in brackets for clarity. ex: [TableName]

Thanks for the help in advance, have a great day!

StankStank
0
Comment
Question by:stankstank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 8

Accepted Solution

by:
drydenhogg earned 150 total points
ID: 18791408
Adding an integer to the PhoneCalls table is not really going to add a huge amount of storage space, given that it will not always hold a value, it would have to be nullable. So will potentially add a small amount of storage to the nullable bitmap size per row, and 4 bytes for the Int.

The PhoneCallsDemos table will take up more space, since it must hold duplicates of the ID values from the other tables. (So if physical hard disk space is a concern, this is already going to take more).

I would personally say your over thinking this one. Business requirements / future requirements would guide whether you wanted to abstract a demo away from a phonecall, such that demos could be linked / arranged to other entities (such as mail shots, direct advertising or whatever - that depends on the nature of your business, something I can not answer obviously)

hth.
0
 
LVL 11

Assisted Solution

by:Goodangel Matope
Goodangel Matope earned 150 total points
ID: 18791638
I think that it will probably be prudent to design a database in the most efficient way whether it is a small database or a big one. Increasing the number of tables is not always going to increase the efficiency of the system. Considering the nature of the system (telemarketing) I would like to make the assumption that you will not be storing hundreds of thousands of transactions every day. I therefore would recommend sticking to fewer tables, and avoid the complexity of adding another table, which would introduce more complex joins, would need indexing and performance optimisation, and other such attention which could be otherwise avoided.
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 75 total points
ID: 18791705
Why not have a foriegn key from demos to phone calls.

Then when a demo is requested, a record goes into Demos pointing at PhoneCalls.

When a demo is not requested, no record goes into Demos

You just need to make sure you use an outer join.


This will only be possibly if a given demo maps to only one phone call. Is that correct? If you ever want multiple clients to come to a single demo, this isn't going to work so well.
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 75 total points
ID: 18792916
It is quite difficult to give sound design advice online because we only partially know of the business requirements imposed by your application.  However, I would suggest the following logical schema instead of the previous....

SK: Surrogate key
PK: Primary key
NN: Not NULL


MARKETER: marketerid(SK), marketer_fname(NN), marketer_lname(NN)
MARKETED: marketedid(SK), marketed_fname(NN), marketed_lname(NN), marketed_phonenumber(NN)
PHONE_CALL: phonecallid(PK, SK), time_called(NN default getdate()), marketerid(FK --> MARKETER), marketedid (FK --> MARKETED)
DEMOS_STARTING: demo_startid(PK, SK), demos_phonecallid (FK to PHONE_CALL, demo_starttime(NN default getdate())
DEMOS_ENDING: demo_startid(FK), demo_endtime(NN default getdate())

Hope this helps...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18793015
Based on the above schema you will need to implement the following constraints:

--> MARKETER: UNIQUE constraint on (marketer_fname + marketer_lname)
--> MARKETER: UNIQUE self incremential counter on marketerid
--> MARKETED: UNIQUE constraint on (marketer_fname + marketer_lname + marketed_phonenumber)
--> MARKETED: UNIQUE self incremential counter on marketedid
--> PHONE_CALL: UNIQUE constraint on (time_called + marketerid + marketedid)
--> PHONE_CALL: UNIQUE self incremential counter on phonecallid
--> DEMOS_STARTING: UNIQUE constraint on (demos_phonecallid + demo_starttime)
--> DEMOS_STARTING: : UNIQUE self incremential counter on demo_startid
--> DEMOS_ENDING: UNIQUE constraint (demo_startid + demo_endtime)
--> DEMOS_ENDING: constraint of demo_endtime > DEMO_STARTING.demo_starttime for the same demo_startid

and of course NO NULL VALUES anywhere...Hope this helps...
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 50 total points
ID: 18795431
A [PhoneCallsDemos] table, consisting of only:

Demo_ID (int, FK), Call_ID (int. FK)

makes sense to me, although I think, based on your description, that it is 1-1 rather than 1-many.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18798468
<<although I think, based on your description, that it is 1-1 rather than 1-many.>>
It is really hard to say given the information provided.  Maybe, the questionner could provide more information about requirements for the system.
0
 

Author Comment

by:stankstank
ID: 18798474
Hey guys,  I was just reading up on some more database design ideas... I am going to stick with your ideas of simply having a FK from the demos table to the phonecalls table.  

Thanks,

StankStank
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 18799773
>> Adding an integer to the PhoneCalls table is not really going to add a huge amount of storage space, given that it will not always hold a value, it would have to be nullable. So will potentially add a small amount of storage to the nullable bitmap size per row, and 4 bytes for the Int.
The PhoneCallsDemos table will take up more space, since it must hold duplicates of the ID values from the other tables. (So if physical hard disk space is a concern, this is already going to take more). <<

Based on the given frequencies of demo assignment, 1 in 10-30 calls, that is just not true.  It will take 4 bytes for every call this way.  The other way it would take 8 bytes for every 10-30 calls, which is definitely less space.

Obviously space alone is not a huge factor here, but it's a fact that the separate table will take less space.  And, much more importantly, is more flexible, as it can later easily accomodate multiple demos being scheduled from one call.
0
 
LVL 8

Expert Comment

by:drydenhogg
ID: 18800048
I think its closer than it looks.

2 x 4 byte Integers
4 bytes overhead unless a unique constraint is applied (if it can be applied)
3 bytes nullability bitmap
2 bytes overhead per record in the array slot

So potentially 17 bytes a row.

In the original question the following was posted :
"To fix this problem, I am proposing to add a table between [Demos] and [PhoneCalls] called [PhoneCallsDemos] which will act as a 1-to-many table.  In doing this, if I wanted to run a report to find all of the calls which resulted in a demo, I could simply do a join from the [phonecalls] table to the [PhoneCallsDemos] table...  Likewise, if I wanted to find all fo the demos which resulted from a phone call, I could do the same thing from the [Demos] table."

So we have to add to this the indexing, since queries will come independently from both sides you can not use a single index, the first column of the index would need to be both fields, i.e. you will end up doing two indexes.

The level of assumption that then has to be applied to get the ratio is getting large, do we assume the index is covering? in which case the storage is going to go up considerably, or just on the single field.

Assuming they are heaps and covering, then the storage would treble. If you used 1 clustered, 1 non-clustered then it's vaguely double (a bit more but lets ignore the levels calculation of the clustered index for simplicity)
34 bytes per row storage overhead (for 2 indexes) and 51 bytes for the heap + 2 non-clustered.

4 bytes the alternative way (or 5 if it knocks the nullability bitmap up 1 byte) at 10-1.

In basic terms its close at 10-1, and possibly better. With a 30-1 ratio Scott is right, it won't takeup as much room.

Made for an interesting bit of maths in my head though.



0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question