Proper database design input needed

U

usul

Hi,

I have a question about proper table design. As a background I am
trying to store marketing information for customers. I have had a
class on Database design in college but I'm having difficulty relating
the proper design to the real world. It doesn't help that the senior
coworker that designed most of the tables I have seen here as an
example only knew excel to start off with either.

Example data that I have is this:
I have one table with the customers basic information, CustNum, name,
phone, address, etc..
As I understand this information for sure would be unique and should
stay in it's own table. What I have trouble with is the extra data
from this survey. Things like:
LTruck (bool, the rest would just be simple true or false if the
customer provides this)
MT
Farm
Mounting
Alignments
Brakes

My original thought was to simply make a new table call it something
like customerSurvey containing fields for each of these data, add on
the CustNum for a reference back to the customer and call it good.
Now I am having second thoughts. What if we decide we want more info
from the customer, like wether they do Shocks, AC Repair and such.
This would require me to change the database structure to add the
extra fields.

My new idea was to simplify the customerSurvey database further. With
fields like:
CustNum
Service
Provided (bool)

This would allow for changes in what we survey without changing the
database structure. My trouble with this is it would result in one
table with a lot of records and many records for one customer. Is
this a better design than the other one? I would think that this one
would make queries on the database slower since it has more records to
sort through.

Any opinions including alternate structure designs would be greatly
appreciated. Thanks
 
D

Duane Hookom

IMHO, use
=============================
My new idea was to simplify the customerSurvey database further. With
fields like:
CustNum
Service
Provided (bool)
=============================
To steal a phrase from John Vinson, "Records are cheap".
 
J

John W. Vinson

Example data that I have is this:
I have one table with the customers basic information, CustNum, name,
phone, address, etc..
As I understand this information for sure would be unique and should
stay in it's own table. What I have trouble with is the extra data
from this survey. Things like:
LTruck (bool, the rest would just be simple true or false if the
customer provides this)
MT
Farm
Mounting
Alignments
Brakes

My original thought was to simply make a new table call it something
like customerSurvey containing fields for each of these data, add on
the CustNum for a reference back to the customer and call it good.
Now I am having second thoughts. What if we decide we want more info
from the customer, like wether they do Shocks, AC Repair and such.
This would require me to change the database structure to add the
extra fields.

My new idea was to simplify the customerSurvey database further. With
fields like:
CustNum
Service
Provided (bool)

This would allow for changes in what we survey without changing the
database structure. My trouble with this is it would result in one
table with a lot of records and many records for one customer. Is
this a better design than the other one? I would think that this one
would make queries on the database slower since it has more records to
sort through.
Click to expand...

Go one step further. Only put a record in this last table if the service is
provided, and omit the boolean Provided field.

John W. Vinson [MVP]
 
U

usul

I think I will use the simplified version. The only thing I don't
like about that is it requires more work to be done on the client
side. In this case the coldfusion server. I guess you will pay for
convenience on either end, it's just a matter of which one would be
easier to modify later on. Thanks for the help all.
 
Top