Help, guidance or just general advice sought

[jwo1985]

DATABASE DESIGN - HELP REQUIRED

I'm trying to design a database for a client of mine who wishes to be able to hold details about particular companies, view their contacts and keep track of all phone calls made and received with these contacts.

My client also wishes to be able to identify links between companies. For instance company1 may have a history of selling their products to company2 but no longer do business with each other so Company2 would be classed as a historic customer of Company1. However Company2 could buy from Company3 instead, therefore would be classed as an "Exisiting Customer".

In summary,
Company1's historic customers = Company2
Company3's existing customers = Company2

...and therefore...
Company2 = historic customer of Company 1 and existing customer of Company3

So I need a database that allows these links to be created amongst companies and I'm not quite sure how to go about getting these links in place. I don't know, for instance, if I'm required to build more tables or add fields to what I've already got as I've pretty much hit a block.

I think from the diagram below the only thing that may be questioned is about the company type. In this case the possible values are historic customer, existing customer, potential customer. However I also need to make it obvious, somehow, which of the companies are clients to the people who I am trying to create this database for.

I'm looking for guidance on how to adjust the tables and relationships to suit the needs of my client. Databases aren't really my strong suit but I should be able to handle anything you throw at me (within reason!) so any help offered would be greatly appreciated.

Thanks for taking the time to look, and if you require any further information please ask.

 

[Newman]

Haven't test it but this should do...
You could create another table (tblCompaniesRelations) with these fields:
CustomerID
SupplierID
boolActive

In the relation page, you show the tblCompaniesRelations twice. For the first one you link CustomerID to CompanyCode, and for the other you link SupplierID to CompanyCode.

 

[Mike Smart]

DATABASE DESIGN - HELP REQUIRED

My client also wishes to be able to identify links between companies. For instance company1 may have a history of selling their products to company2 but no longer do business with each other so Company2 would be classed as a historic customer of Company1. However Company2 could buy from Company3 instead, therefore would be classed as an "Exisiting Customer".

In summary,
Company1's historic customers = Company2
Company3's existing customers = Company2

...and therefore...
Company2 = historic customer of Company 1 and existing customer of Company3

So I need a database that allows these links to be created amongst companies and I'm not quite sure how to go about getting these links in place. I don't know, for instance, if I'm required to build more tables or add fields to what I've already got as I've pretty much hit a block.

Hi JWO

You need two more tables. Association and ContactsAssociation.

The Association table contains AssociationId and AssociationDescription (as a minimum). Examples would be Historic Customer|Existing Customer|... and any other inter-company relationship you need in future.

The ContactsAssociation table has a concatenated primary key made up of AssociationID, ContactID and ContactID2 (ugh hate that but not sure how else you can work-around). If you wanted to add anything relating to the association (for example the dates it applied between) they'd also go into ContactsAssociation as fields. Re-draft your schema and post if back if you like.

You might get some mileage from the field and table naming conventions detailed in the quality design guidelines on my site too, there are some inconsistencies there at the moment and you have a one-off chance to fix that at the design stage.

 

[jwo1985]

The ContactsAssociation table has a concatenated primary key made up of AssociationID, ContactID and ContactID2 (ugh hate that but not sure how else you can work-around). If you wanted to add anything relating to the association (for example the dates it applied between) they'd also go into ContactsAssociation as fields. Re-draft your schema and post if back if you like.

Is this any better?

I've tried to follow what's been said but I think I'm right in saying that the Association Tables should concern the Companies and not the Contacts. After all I'm not looking to have links between the different contacts... its the businesses that are trading with one another and therefore my reasoning is that this is where the associations/links should occur. Please correct me if I am misreading your advice though!

Thanks for your help so far, I've also had a go at renaming the fields within the tables. I appreciate any feedback, criticism or advice!! Thanks!

 

[Mike Smart]

Hi JWO

Here's what I meant (see attachment). The primary key in the join table is compound, made up of three foreign keys (data type number).

Great job with your table/field naming by the way. It is great to see my design rules being used! Only thing I'd change is Contacts and Calls in singular rather than plural and lose the space in Company Types (for reasons behind this see the design standard).

If the schema isn't clear just ask. I think you'll easily be able to query for anything you need with this model but to test add three rows of sample data to each table and then prove that it produces the reports you need easily.

 

[jwo1985]

Thanks! Certainly looks promising... hope to give it a more thorough testing when I'm provided with some more data from my client. I've also renamed the fields that you identified as being a bit slack in your latest post.

It's all very much appreciated!!