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 -------------------
- PK_Demo_ID (int)
- DemoStart (smalldatetime)
- DemoEnd (smalldatetime)
- FK_Result_ID (tinyint)
- more fields...
- 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!