[thelist] Access DB design troubles

[Luther, Ron]

Tim asked about a db design:

>>I'm trying to design an Access DB that will hold customer details.
>>My problem is that I want to hold details of who referred each customer,
>>where each referred is a customer that is currently on the database.


Hi Tim,

I know some folks have already said "No problem" and while I agree that it 
_can_ be made to work, I think there is more to do or think about:

(1) I would probably think about adding one or more 'dummy' rows that you 
could match to new customers who were referred by "no one", or "print ad", 
or "other".

(2) I think the referrer matching is tougher than folks are leading you 
to believe:

(a) Obviously, you can't/shouldn't/won't offer a drop-down containing the 
names of all of your current customers to 'pick from' for the referrer field. 
That's a fairly major 'bad idea' (TM). Which pretty much means it's going 
to be a write-in field.

(b) Write-ins means you will have a fun time matching my manually entered 
referrer field value of "Tim Burgan" to your db entry of "Timothy Burgan". 
[And that's assuming I get the name right and don't misspell an "i" for that 
"a" near the end of your last name!]

(c) How are you going to properly assign a referrer to the 'correct' Tom Jones, 
once you have six different customers going by that name?


I think I would suggest adding an "error table" to throw mismatches/no finds/ multiple finds (more than one match for the same name) into and adding a 
'business process' step to manually review that table on a regular basis.


Good Luck & HTH,

RonL.