Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What's the database platform? –  Thomas Stringer Sep 16 '11 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. –  Levu WebWorks Sep 16 '11 at 2:20

1 Answer 1

up vote 0 down vote accepted

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:

SELECT *
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
share|improve this answer
    
Your design works perfectly. Thanks. –  Levu WebWorks Sep 16 '11 at 19:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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