Help me define the table relationships for data with multiple structures. I need to track leads. Leads come from multiple sources. Each lead source has its own data structure. I need advice representing this in a relational database.
Each record in the TABLE "Products Sold List" (defined below) has a lead source. The leads can come from Organizations, Doctors, Company Office, and Company Employees. Here are the different types of data that constitute a lead.
TABLE Organization: - Name -- TEXT TABLE Doctor: - NPI# -- INTEGER (unique for each doctor. I planned on using this as primary key) - FirstName -- TEXT - LastName -- TEXT TABLE Company Office: - Location -- TEXT TABLE Company Employee: - Department -- one of {Sales, Technician, QA}. Note that the employee is not tied to an office. Office leads are leads that come from misc office staff at the various locations. - FirstName - LastName Here is TABLE Products Sold List: - [misc fields] - PrescribingPhysicianNPI > many-to-one > Doctor.NPI -- "doctor that prescribed the product for the patient" - SalesRep > many-to-one > [Company Employee].PrimaryKey -- "Sales Rep credited for the sale. Needed for commissions calculation" - LeadSource > ???
Misc information:
The company offers incentives to leads (affiliate commission) and incentives to sales staff (sales commission). Leads coming from Organizations and Company Offices don't get paid affiliate commission. I am considering merging TABLE Company Office into TABLE organization and using "Office — " as the data to track different offices (i.e.: Organization.Name = "Office — Cincinnati"). Bad idea? Issues?
Best Answer
This might force a bit of change for your tables, but we do something like this at work:
Ensure each table has a int/guid primary key of some sort:
Organization Table
Then create a type-table to house your different LeadSource types:
Then on your ProductSoldList Table add 2 columns
LeadSourceID
andLeadSourceType
Then you can easily query the
ProductSoldList
TABLE and know which LeadSource to join back to: