Design problem - too many fields?

[haydnw]

I am designing a database to be used to help us emailing
customers. They can sign up to be emailed about specific
products, but I'm not sure how to store the information
about which models each customer wants to be emailed about.

The way I see it, I need to have a record for each
individual customer which describes what they want.
Therefore in that table I would need Yes/No fields for
every single model of product which we sell. So it would
be:

Model1 Model2 Model3 Model4
Customer A x x x
Customer B x
Customer C x x
Customer D x x x x

However, every time we stock a new product, I would need
to add a new field to the table - we simply have too many
products! Please could someone give me some input as to
how you would conquer / have conquered this problem, as
it's driving me mad and holding my entire project up!!

Thanks,
Haydn

 

[Cheryl Fischer]

Haydn,

However, every time we stock a new product, I would need
to add a new field to the table - we simply have too many
products!

You have just defined why the table structure you are attempting to use is
considered un-normalized and is never recommended for a database
application. Briefly, here is what I think you need to get started:

Your customer email table:
CustomerID (AutoNumber type recommended)
CustomerName
CustEmail
....other fields directly related to customer identification

A CustInterests table:
InterestID (AutoNumber)
CustomerID (Long type - Indexed Duplicates Allowed)
ProductInterest

To read up on and get a better feel for Normalization, here are a couple of
links:

Normalizing Your Database: First Normal Form
http://databases.about.com/library/weekly/aa081901a.htm

"Database Design for Mere Mortals" Amazon link
http://tinyurl.com/2c6bp


hth,

 

[haydnw]

Hi Cheryl

Thanks for the quick response! Your suggestion is exactly
what I needed. I knew I was barking up the wrong tree but
couldn't get the correct structure to pop into my brain.
The worst part is that I've done so many other databases
with exactly the structure you describe in the past. Must
be all this unusually cold weather here in the UK!

Thanks again for unblocking my brain!

Regards,
Haydn

 

[Cheryl Fischer]

You're welcome - and good luck with your project.