Design Question

Hello,

I have started the design for a new project where financial advisors can
store the customer info.
The problem is , there have to be stored about 100 different information
for each
customer , arranged in about 20 categories. some categories have like one or
two answers,
but some can have more.

For example questions about jobs, IRA, wishes and goals, properties,
investing, ...

I have thought to put those categories with several possible answers in
seperate tables, but
what to do with those categories where I can have only one or two answers?


regards, alex

In article ,
says...
Hello,

I have started the design for a new project where financial advisors can
store the customer info.
The problem is , there have to be stored about 100 different information
for each
customer , arranged in about 20 categories. some categories have like one or
two answers,
but some can have more.

For example questions about jobs, IRA, wishes and goals, properties,
investing, ...

I have thought to put those categories with several possible answers in
seperate tables, but
what to do with those categories where I can have only one or two answers?


regards, alex




Hi Alex,

This sort of requirement often needs a flexible "Attribute" database
design. The advantage is that you can add new types of information
without changing your database structure. The disadvantage is that it
is more difficult to do special editing or calculations on the attribute
values - they are usually just text, although sometimes people store
both a text and a numeric field for each AttributeValue. For specific
values that you may do calculations on (e.g. AnnualIncome,
RetirementAge), you may want to store them in the Customer table
instead.

-- tblCategory
CategoryKey
CategoryName "Employment", "Wishes"

-- tblAttribute
AttributeKey
CategoryKey
AttributeName "Current Employer", "Desired Retirement Age"

-- tblAttributeValue
AttributeValueKey
CustomerKey
AttributeValue "Microsoft", "65"


Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

Something similar to Armen's recommendation is "At Your Survey" which can be
found at http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.
Feel free to steal ideas as you like.

--
Duane Hookom
MS Access MVP
--

"Armen Stein" m wrote in
message ...
In article ,
says...
Hello,

I have started the design for a new project where financial advisors can
store the customer info.
The problem is , there have to be stored about 100 different information
for each
customer , arranged in about 20 categories. some categories have like one
or
two answers,
but some can have more.

For example questions about jobs, IRA, wishes and goals, properties,
investing, ...

I have thought to put those categories with several possible answers in
seperate tables, but
what to do with those categories where I can have only one or two
answers?


regards, alex




Hi Alex,

This sort of requirement often needs a flexible "Attribute" database
design. The advantage is that you can add new types of information
without changing your database structure. The disadvantage is that it
is more difficult to do special editing or calculations on the attribute
values - they are usually just text, although sometimes people store
both a text and a numeric field for each AttributeValue. For specific
values that you may do calculations on (e.g. AnnualIncome,
RetirementAge), you may want to store them in the Customer table
instead.

-- tblCategory
CategoryKey
CategoryName "Employment", "Wishes"

-- tblAttribute
AttributeKey
CategoryKey
AttributeName "Current Employer", "Desired Retirement Age"

-- tblAttributeValue
AttributeValueKey
CustomerKey
AttributeValue "Microsoft", "65"


Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/AS...jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

Duane Hookom wrote:
Something similar to Armen's recommendation is "At Your Survey" which
can be found at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.
Feel free to steal ideas as you like.

I'll be happy to take that offer.

This wasn't the easy solution I hoped/whished for, but if it's the way to
go.

Thanks to both of you Duane and Arnem for you advice.

regards, alex