markmeikle
New member
- Local time
- Today, 07:46
- Joined
- May 22, 2007
- Messages
- 1
I need help organizing my data and relationships properly from the ground up. I am having trouble balancing sales relationships with operational relationships.
I am building a database to manage sales leads, clients, and projects for my music recording company.
We serve churches, schools, community groups, and individuals by recording and producing their music albums. We also handle song licensing so their recording is legal.
Normally I'd start out with a table for our clients. The problem is "who our client is" is actually quite confusing.
Our sales leads consist of music directors who work at schools and churches. Often the directors work at a church AND a school. We typically bill their organization (not the director). To add confusion we bring individual artists into our studio to record.
I want the DB to manage sales leads as "Contacts". I want to associate "Contacts" with "Organizations". I need to establish when a "Contact" or "Organization" or whoever our client is actually becomes a client and distinguish them. I have to tie in "Projects" somewhere!
How should I divide these tables and relate them considering the following complicating requirements? I don't like that I'm repeating data in the "Organization" tables of School, Church, Community, and Individual.
-Each project may have multiple artistic contacts
-Each artistic contact may work at multiple organizations (a church and a school)
-Each organization usually has multiple artistic contacts. This matters because one contact might be a client where another is a lead, yet both work for one organization that is paying us!
-Typically each project and each organization has one billing contact
-Each project's artistic contact typically (BUT NOT ALWAYS) works at a larger organization such as a church or school with other potential sales leads.
-We serve four types of client each with subtle differences. For churches we want to keep congregation size and number of services, for schools we want to know the mascot and colors. Right now I've divided these into different tables. Is this right?
Thank you!
Mark
I am building a database to manage sales leads, clients, and projects for my music recording company.
We serve churches, schools, community groups, and individuals by recording and producing their music albums. We also handle song licensing so their recording is legal.
Normally I'd start out with a table for our clients. The problem is "who our client is" is actually quite confusing.
Our sales leads consist of music directors who work at schools and churches. Often the directors work at a church AND a school. We typically bill their organization (not the director). To add confusion we bring individual artists into our studio to record.
I want the DB to manage sales leads as "Contacts". I want to associate "Contacts" with "Organizations". I need to establish when a "Contact" or "Organization" or whoever our client is actually becomes a client and distinguish them. I have to tie in "Projects" somewhere!
How should I divide these tables and relate them considering the following complicating requirements? I don't like that I'm repeating data in the "Organization" tables of School, Church, Community, and Individual.
-Each project may have multiple artistic contacts
-Each artistic contact may work at multiple organizations (a church and a school)
-Each organization usually has multiple artistic contacts. This matters because one contact might be a client where another is a lead, yet both work for one organization that is paying us!
-Typically each project and each organization has one billing contact
-Each project's artistic contact typically (BUT NOT ALWAYS) works at a larger organization such as a church or school with other potential sales leads.
-We serve four types of client each with subtle differences. For churches we want to keep congregation size and number of services, for schools we want to know the mascot and colors. Right now I've divided these into different tables. Is this right?
Thank you!
Mark
