Solved

DB Design: am I right on this design?

Posted on 2007-03-25
10
235 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
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 8

Accepted Solution

by:
drydenhogg earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 75 total points
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
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
Comment Utility
<<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
Comment Utility
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:ScottPletcher
Comment Utility
>> 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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now