Proper database design input needed

[jdharbour]

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

 

[Michael Gramelspacher]

CREATE TABLE Customers
(customer_id INTEGER NOT NULL,
PRIMARY KEY (customer_id));

CREATE TABLE Services
(service_name VARCHAR(30) NOT NULL,
PRIMARY KEY (service_name));

CREATE TABLE CustomerServices
(customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
service_name VARCHAR (30) NOT NULL
REFERENCES Services (service_name),
provided YESNO DEFAULT 0 NOT NULL,
PRIMARY KEY (customer_id, service_name));

Query: Fill Customer Services

INSERT INTO CustomerServices ( customer_id, service_name )
SELECT Customers.customer_id, Services.service_name
FROM Customers, Services;

Just an opinion.