I can't figure out the best (normalized) way to represent the
following data, despite the fact that it seems like a simple and
frequent requirement.

My current database holds Customer information. I need to track how
customers have heard about us. Here are some of the ways:

Credit Union Referral
Credit Union General
Web
Word of Mouth
Advertising

Now, *beyond* that, I need to include conditional data. If it's a
Credit Union Referral, I need to know which credit union, the loan
officer, etc. If the Web, I need to know whether it came from our
website, a search engine, etc. If Advertising, blah blah.

This is what I don't know how to model.

Thus far I have a [Customers] table and a [SourceTypes] table, related
by a SourceTypeID field. That's all basic, and takes me up through
the first part of my model. It's the conditional information that I
don't know how to properly represent. I have ideas, but they don't
seem to be normalized or I see serious problems down the road in
manipulating the data.

Any ideas?

Have you thought about using structure:

tblCustomer
*CustomerID
*CustomerName
etc

tblReferral
*ReferralID
*CustomerID
*ReferralSource

tblReferralData
*ReferralID
*ReferralCondition
*ReferralConditionData

Basically, store all the Customer data in tblCustomer. Store the
referral type in tblReferral (or you can push this up to tblCustomer
if you are only ever going to track the Primary referral source).
Store the conditional data in the tblReferralData.

Set up a lookup table that has all the conditions for each type of
referral so that when you get a new customer you can prepopulate
tblReferralData with all the Questions that need to be answered.

It may make data manipulation a little more difficult, but life will
ultimately be easier when you identify a new referral method and/or
new conditional data you need to collect for a given referral method.

Tom

On Feb 22, 12:42 pm, Tom <rtmn... (AT) swbell (DOT) net> wrote:
Tom,

I don't understand how this will work. Let me clarify my thoughts.

1. I think I would put the SourceTypeID in the Customers table, since
every customer will have one and one only.

2. In your hypothetical tblReferralData table, I am imagining that I
would have to either:
a. Lump together all fields for every different higher level "node"

[tblReferralData]
CU
Employee

Our site
Craigslist
Search Engine

Yellow Pages
Promotion

...and then information would be populated in the proper field, with
the rest left null, which is not normalized

or

b. Create another table, [SourceTypeSpecific] with all those different
fields moved to it, and then relate them to [tblReferralData]

so [tblReferralData] would be
*[SourceTypeID]
*[SourceTypeSpecificID]



Am I understanding you correctly? If I am, I still feel as if the
structure is "wrong" and will cause problems later, but that's more
intuition speaking than experience, and I could be wrong.

Hi John,

Well your question kind of leans down the line towards a classic many-
many relationship.
However keep in mind that in real business situations rules of
normalization are often discarded in favor of a number of other
factors including performance, UI considerations etc.
ASSUMING that each customer will have a single REFERAL SOURCE and that
each REFERAL source will have any number of SOURCE TYPES it really
simple to build 2 tables that will serve as record sources for combo
boxes for the user to select from. The input from these combo boxes
will of course update the corresponding fields in the customer DB.

So - for example.....

CUSTOMER DB (fields)
Custno
Ref_source
Source_type
etc etc

REFSOURCE DB
REF_sourceID (long integer)
REF_desc (text)
( I like to use numeric codes with a second field for the text
description - speed & storage reasons)


SOURCETYPE DB
SOURCE_TYPEID (long)
REF_sourceID (long)
Source_Desc (text)

The contents of these tables might look like this.........

REFSOURCE DB
10 Internet
20 Yellow Pages
30 Newspaper

SOURCETYPEDB
---------------------------------------------------------------------------------------------------------------
SOURCE_TYPEID REF_sourceID Source_Desc
100 10 Our
Website
110 10 Yahoo
Search
120 10
Google search
200 20 Local
phone book
210 20
Another alternate book
etc etc

so now the source for your combo boxes become...........

cmbRefsource:
"select * from REFSOURCE"

cmbSourceType:
"select * from SOURCETYPE where REF_sourceID = [yourform]!
[cmbRefsource]"

Hope this makes sense and helps - hard to illustrate easily in text
only format.

Charlie

On Feb 22, 2:30 pm, JohnH <JohnHarri... (AT) gmail (DOT) com> wrote:

With one and only one SourceTypeID, I would structure as follows...


tblCustomer
*CustomerID
*CustomerName
*SourceType
*etc


tblReferralData
*CutomerID
*ReferralCondtion
*ReferralData

With an additional lookup table

lkpReferral
*SourceType
*ReferralCondition


When you add a new record to tblCustomer, your routine would include
querying in all the ReferralConditions from lkpReferral fort the
appropriate SourceType to tblReferralData and forcing the user to
answer all the questions before the add routine is done.

Example:
Assume the following Conditional data is required for Credit Union
referrals:
*Credit Union Name
*Referring Employee Name
* Referring Employee Telephone Number

Then if I am a new customer, and I heard about your service from a
Credit Union, your data entry would be

tblCustomer
*CustomerID - autonumber - 987
*CustomerName - Tom M~~~~
*CustomerStreetAddress 123 Anystreet
*etc
*SourceType - Credit Union

The new record routine would include a query that would populate
tblReferralData with the following 3 records:

tbReferralData
record 1
*CustomerID - 987
*ReferralCondtion - Credit Union Name
*ReferralData - null

record 2
*CustomerID - 987
*ReferralCondtion - Referring Employee Name
*ReferralData - null

record 3
*CustomerID - 987
*ReferralCondtion - Referring Employee Telephone Number
*ReferralData - null

The data entry person would then be forced to enter values for all 3
of the nulls before data entry is complete. You can prevent nulls by
requiring answers to the questions to complete the new record
routine. If you really want to enforce data integrity, you could
collect all this info in an unbound form, and have code to commit the
data to the table, that was you could enforce no nulls at the table
level for ReferralData

It may seem redundant to store the ReferralCondtion in tblReferralData
when you could add an ID field to the lookup table, then just store
the ID in tblReferralData - I don't like doing that because inevitably
somebody well meaning or otherwise will get in and edit the lookup
table and change an existing question instead of simply adding a new
one. Then your entire data set is messed up.

You can also skip the part querying in the specific questions you want
answered from the lookup table and simply provide a combobox with a
recordset derived from the lookup table filtered to the applicable
question. The data entry person could then pick the one he wanted to
answer. This is simpler than the method I originally described, but
it lacks the ability to force the data entry person to answer the
questions you want answered (i.e., they could just skip this part).

This may seem like overkill on the setup, but when it comes time to
analyze the data, it will be a breeze.

HTH

Tom

JohnH, I think what you think of as conditional data goes hand-in-hand
with functional dependency when it comes to normalization.

Imagine, for just for a moment, having your existing [Customers] table
and adding a [Credit_Union_Referral] table, a [Credit_Union_General]
table, a [Web] table, a [Word_of_Mouth] table, and an [Advertising]
table. You'd relate your [Customers] table to all the other ones with
a SourceTypeID field. This way, you could fill all the tables with
their corresponding conditional data fields..as many fields as you
wanted. The [Credit_Union_Referral] table would have a field for name
of credit union, loan officer, etc.

One important thing to remember: even if you were to throw out the
whole relational model and begin with an ordinary spreadsheet, it
would work...and it would work well. you could stack the fields right
next to each other...Customer_ID, Credit_Union_Referral (yes/no),
Credit_Union, Loan_Officer, Credit_Union_General, Web, Website,
Search_Engine, etc. The only caveat would be that you'd have a lot of
blank cells for any given Customer, depending on their Source Type.
When you think about it, you have no risk redundancy, which is the
initial reason for normalizing a database.

Everyone else seeems to be suggesting a simpler 3-table structure, and
I could see that working as well. The only problem with that is
having to generalize and fit all the possible conditional data fields
into a generic [Referral_Data] table. I think this would put you in
the same awkward situation you're in now with having all these
conditional data fields on your hands.

I disagree...

Any design that uses the approach of using the conditional questions
as the field name (e.g., a field for CreditUnionName and another field
for WebSearchEngineName, etc), be it in separate tables for each
referral type, one child table for all referral types or simply in the
main customers tables suffers from a common problem. Next week, next
month or next year when somebody has the bright idea to add another
conditional question you have to:
*Add a field to your table
*redo your forms to include the field
*redo your queries to include the field
*redo your reports to include the field
*possibly rework code to include the field

This approach incorporates the worst of a spreadsheet and a database -
flat file structure that requires significant effort to redesign when
a new type of information is needed with the increased complexity of a
database.

If instead you have a structure where you store the question and the
answer as data, add questions to your hearts content - no redesign
necessary. This portion of the database is fundamentally no different
from designing a survey or questionnaire (the customer data may be,
but that wasn't in the scope of the original question). Most well
designed survey databases that I have seen don't have a dedicated
field to store the answer to each question...

Tom

On Feb 25, 7:43 pm, Tom <rtmn... (AT) swbell (DOT) net> wrote:
Data Entry Outsourcing provides time bound, cost effective and
qualitative Data Entry also provides numeric data entry, textual
data entry, image data entry, data format, data conversion and also
online data entry, offline data entry with 99.98% accuracy and time
bound.

Data Entry Services also provides form data entry, data capture, HTML/
SGML coding, image scanning, file conversion with low cost,high
quality, 99.98% accuracy and time bound.

Data Entry Outsourcing provides data in JPG data format, XML data
format, HTML Data format, Excel data format, DBF data format, Word
data format etc. OCR and ICR Image Processing and Electronic
Recognition services provides by data entry outsourcing in UK.

Do visit http://www.dataentryoutsourcing.co.uk to know more about data
entry services and offer.
E-mail Us : info (AT) dataentryoutsourcing (DOT) co.uk
Phone (India): +91-794-000 3000
Fax : +91-794-000 3002.