Creating a customer database incorporating HQ and branches.

[neileg]

I'm not in the least bit upset, I just know you're making a mistake. It's exactly the kind of mistake that I used to make so I'm not being holier than thou! There are often different correct solutions to any given problem, it's just that yours isn't a correct solution.

I do subscribe to the concept of 'fit for purpose'. That means that something doesn't have to be perfect to be good enough. The 'rules' of db design exist because there are established approaches to most of the types of application you want to develop. If you're going to knowingly break the rules (and we all do from time to time) then you should be certain that there is a reason for doing this.

 

[CarysW]

Thanks Neil.

Would you be able to tell me the possible pitfalls of what I plan to do as to me it seems ideal for what I want.

It doesn't need to be something complex, just somewhere to store customer data.

The brief was simply to take the data we had and store it in a more efficient way. The branch information is a separate thing that I'm adding but does not need to be used for the same purpose.

After consultation with the other people who will be using it we have decided this method will be simplest.

I'm guessing there may be small problems along the way but this is all part of the learning curve. If you really do see a major pitfall please lend me some friendly advice.

Thanks

 

[neileg]

I'm expecting that the location data for HQs and branches will be the same. That's why you only need one table. You need a field that indicates which location you regard as the customer's HQ. When you want to do a mail shot, you use a query that extracts all those addresses that are flagged as HQ.

Why?
- you only need one table, you only need one data entry form, you maximise efficiency
- if a customer changes its HQ to what used to be a branch, you only change the HQ field
- if you decide you do want to mail out to all locations, you just ignore the HQ flag

Like I said before, you'r restricting yourself to a model that has only one buyer per company and always at the location you regard as HQ. There's no need to build in this restriction. Have a table for buyers and link this to your location table by holding the location ID in the buyer table.

Why?
- this allows you to have as many buyers as you need and at any location
- if a buyer moves location you just change the location ID

These are just simple changes but you end up with fewer restrictions and no real downside.