database tables for each customer or database design with linked tables?

Question

I'm just new to (mysql) database design, and would appreciate your feedback with respect to the following question. I'm developing a website linked to a mysql database that should contain customer info and product info. For general customer info (name, address, date-of-birth etc) I've created a table 'crm', for general product info (description of products available) I've created a table 'products'.

My question is how to store the list of products selected by each customer? This list will of course vary per customer and can also change over time. Furthermore, the table 'products' may alo change over time (new products being added or existing products being removed). First, I though about a design with a 'customer product' table with selected products for each customer, but after reading some comments on related pages on this site I question whether that's a good design (as this may result in over 100K tables). An alternative solution may be a design with single table 'customer products' using links like this:

table 'crm'                    table 'customer products'          table 'products'
--------------------------     -------------------------------    ---------------- 
name | addr | first_prodref    prodref | prodid | next_prodref    prodid | name | cost


- first_prodref in table 'crm' points to (index) prodref in table 'customer products'
- prodid in table 'customer products points to (index) prodid in table 'products'
- next_prodref in table 'customer products' points to the next prodref in table 'customer products'

Would this alternative solution be ok or are there better suggestions?

Appreciate your feedback.

Answer 1

Here's something to get you started...

• (P) means a Primary Key
• (F table.column) means a Foreign key and the table.column it should point to

We need a table to store addresses: customer billing addresses, customer shipping addresses, etc.

addresses
    id              unsigned int(P)
    street1         varchar(75) // 123 South Main Street, etc.
    street2         varchar(75) // Apt A, etc.
    city_id         unsigned int(F cities.id)
    zip             varchar(6) // 12345, A1A 1A1, etc. (CA, MX and US)

It's best to let users choose from a list of values than it is to let them type in the value (ie city name) themselves...

cities
    id              unsigned int(P)
    state_id        unsigned int(F states.id)
    name            varchar(50) // Omaha, Detroit, Tampa, etc.

Again, it's best to provide valid values to choose from rather than let users enter their own value... See ISO 3166-1.

countries // 
    id              char(2)(P) // CA, MX, US, etc.
    iso3            char(3)(U) // CAN, MEX, USA, etc.
    iso_num         char(3)(U)
    name            varchar(50)(U) // Canada, Mexico, United States, etc.

You will want to add more information to this table for sure but here's some columns to get you started... see PHP's crypt() function for how to hash the password.

customers
    id              unsigned int(P)
    first_name      varchar(50) // John, Mary, etc.
    middle_name     varchar(50) // Quincy, Louise, etc.
    last_name       varchar(50) // Doe, Public, etc.
    email           varchar(255) // me@privacy.com, etc.
    username        varchar(32) // blahblah, etc.
    password        varbinary(255) // hashed
    ...

This table connects a customer to an unlimited number of addresses.

customers_addresses
    id              unsigned int(P)
    customer_id     unsigned int(F customers.id)
    address_id      unsigned int(F addresses.id)

You will want to add more information to this table but here's some columns to get you started...

orders
    id                  unsigned int(P)
    created             datetime // 2013-08-28 13:24:53, etc.
    shipped             datetime // 2013-08-28 15:12:10, etc.
    customer_id         unsigned int(F customer.id)
    ship_address_id     unsigned int(F addresses.id)
    bill_address_id     unsigned int(F addresses.id)

We need a table that ties an order number to all the products that were part of each order.

orders_products
    id              unsigned int(P)
    order_id        unsigned int(F orders.id)
    product_id      unsigned int(F products.id)

You will want to add more information to this table but here's some columns to get you started...

products
    id              unsigned int(P)
    name            varchar(50) // Widget A, Widget B, etc.
    height          unsigned int // height in inches, centimeters, whatever.
    width           unsigned int // width in inches, centimeters, whatever.
    depth           unsigned int // depth in inches, centimeters, whatever.
    weight          double // weight in ounces, pounds, grams, kilograms, whatever.

Like cities and countries, let the user choose from a select list rather than enter potentially bad data. See ISO 3166-2.

states
    id              unsigned int(P)
    country_id      char(2)(F countries.id)
    code            char(2) // AL, NF, NL, etc.
    name            varchar(50) // Alabama, Newfoundland, Nuevo León, etc.

Answer 2

You are correct that you do not want to make a table for each customer and that you will use a single table to link customers to the products that customer has "selected". I am not clear from your question how a customer selects a product. Perhaps it is by visiting that product page on your website but a more typical scenario would be an order or an invoice. In either situation you would have two tables. eg Invoices and Invoice_Items or Orders and Order_Items. The "Items" tables join the customers to products via "foreign keys" in columns in the items tables that link to the primary keys of the "reference" tables.

Now you can show Items ordered by each customer or items purchased by each customer. You can use the same concept to show items visited/reviewed by each customer, either per site visit or lifetime.

Answer 3

Read up on the database normalization and the reasons to at least get to 3rd normal form.

In your exammple:

CRM
CRM.UniqueKey

Products 
Product.UniqueKey

customer_products (CP)
CRM.UniqueKey        -| combined make a composite unique key (unless you need to track history then you may want to
Product.Unique Key   -| add a start date )
CP.StartDate         -|
CP.EndDate           -| Start and end dates allow customers to add remove products active ones would always have                  
                     -| end date of null (but only do this if history is needed)

Customer_products is an Associative table between two primary tables and resolves the many-to-many relationships between customer and products.