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?

  • What's the database platform? Sep 16, 2011 at 0:18
  • MS Access 2010 for now. I could force the use of MSSQL if I absolutely must. I am trying to keep the database in Access because the managers who use this data can write queries using the Access GUI. Sep 16, 2011 at 2:20

1 Answer 1


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

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.