Marketing Database Design Help

[jesusoneez]

Hi everyone.

I've just started a new role as Database Manager, and have been tasked with identifying problems with their current, 15 year old relational database (4D). Generally speaking it's a contact management database designed for targeting specific business markets for specific conferences and events. The problem I have with it is that a company on the database can only be assigned one business type, when many companies can come under several business types. The result of this is that we're not getting through to as many potential customers as we would like.

Apart from adding two or three more business type fields into the database (making searching a bigger pain in the rear), can anyone think of any other way to handle this from a theoretical perspective? Like I say, apart from adding more business type fields, or greatly generalizing the business types themselves (the result being reaching more potential customers, but likely reaching many of the wrong type and increasing costs), I'd be interested to see if anyone has other ideas.

It may also be worth noting that the database is a contact-centric database as opposed to a company-centric database...I'm thinking it may work better as a company-centric database but that's a different issue.

Thanks,

Steve

 

[Rabbie]

At the moment it seems you have a Many to 1 relationship between companies and business types. Ie. A business type can refer to many companies but a company can only have 1 business type. It sounds as if that this should be a Many to Many relationship best modelled through a junction table. This would allow a company to have more than 1 business type

 

[jesusoneez]

That sounds interesting.

Having had a very quick trawl on the net for the definition of a junction table. At the moment their is a company table (holding company data), and business type table (holding business codes and their description). This is currently linked by a limiting one to one relationship.

A junction table seems to me to be a third, intermediary table, whereby the business codes for a company are stored. So instead of their being a business type field in contacts, you'd add many business codes to the intermediary (or junction) table linked by company code (companies UID). Something like that?

So you end up with a third table something like this.

COMP BCODE
001 PR
001 ME
001 HL
002 SO
002 LO
003 PR
004 SO
004 SO

Blah...have I got the concept about right?

Thanks,

Steve

 

[Rabbie]

That sounds interesting.

Blah...have I got the concept about right?

Thanks,

Steve

Seems spot on to me. Then by using queries you can quickly find the Companies in a particular business type.

 

[jesusoneez]

Thanks for all your help and quick response.

Steve