Relationship 'leads', 'contacts' and 'accounts'
Hi, i'm new to FM and despite going through all of lynda.com's courses on the subject, i still have difficulties understanding how to design my database and the relationship between some of the tables i foresee.
I have a leads-table containing each and every of the prospects i'm calling duriing my cold-calling. It contains just basic info helping me to follow-up on the lead until it's either dead or becomes an account.
So i have many leads and occasionaly one becomes an account.
The leads table contains very basic stuff like the company name, one contact person's name, a telephone number and a key identifier which is the url of the company's website.
The accounts table contains all the relavant information about the company (once they became a customer).
I also plan to have another -third- table called 'contacts' where i want to store all the contact details of each of the employees i deal with within an 'account'.
So, many leads with occasionally some becoming accounts.
And many contacts with each of them always related to at least one existing account.
The purpose is also to re-use as much as possible existing data when promoting a lead to the accounts table, so i avoid re-typing all the already available info when converting a lead to an account.
My question is about relationships: How should i design this database? What would be primary keys etc? I'd be very helpful if someone could provide me with a simple diagram showing me how to best address this.
I'm not sure if this is relevant, but over time, as i find more time and get more experienced with FM, i'd like to add extra tables for products, proposals, invoices & project management....