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

i'm new (mysql) database design, , appreciate feedback respect following question. i'm developing website linked mysql database should contain customer info , product info. general customer info (name, address, date-of-birth etc) i've created table 'crm', general product info (description of products available) i've created table 'products'.

my question how store list of products selected each customer? list of course vary per customer , can change on time. furthermore, table 'products' may alo change on time (new products being added or existing products being removed). first, though design 'customer product' table selected products each customer, after reading comments on related pages on site question whether that's design (as may result in on 100k tables). alternative solution may design single table 'customer products' using links 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 (index) prodref in table 'customer products' - prodid in table 'customer products points (index) prodid in table 'products' - next_prodref in table 'customer products' points next prodref in table 'customer products' 

would alternative solution ok or there better suggestions?

appreciate feedback.

here's started...

• (p) means primary key
• (f table.column) means foreign key , table.column should point to

we need table 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 , us) 

it's best let users choose list of values let them type in 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 provide valid values choose rather let users enter 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 want add more information table sure here's columns started... see php's crypt() function how hash 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 customer 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 want add more information table here's columns 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 table ties order number products 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 want add more information table here's columns 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 , countries, let user choose select list rather 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.