database design - Best way to deal with Big data in mysql -

current design

previously co-worker designed databse had tables customer_0, customer_1.... customer_9 wherby customer ids split 10 different tables based on last digit of id.

the problem design:

1. i dont find standard practice
2. to deal it, have create queries strings, in stored procedures or in code, pass in id , query created @ runtime extracting last digit of id , choosing table query from.
3. to apply foreign key constraint need have referenced tables split (i wouldnt use term partitioned here because type of splitting not partitioning) in same fashion if not intended store huge data, e.g. customer_sales tables have split 10 parts since have apply foreign key constraints. (a customer has one-to-many relation custoemr_sales)

my design

on tring figure out work-around came know can table partitioning solved problem. refereing this question.

the prblem partitioning approach

now problem approach cannot have foreign key constraint anyway in partitions, doesnt solve problem.

db sharding or "shared nothing"

then came across this, in use schema replication , understand replicate schemas on different physical locations, , respective schema queried based on specific shard key.

my question

what should now, cannot let go of foreign key constraint, choosing table partitioning. should let go of partitioning , sharding , focus on conventional schema, , leave sharding part dba?

note: max expected customer base 10 million.

yes, let go of partitioning , sharding now--stick conventional, simple schema. you've got many easier fruit pick can performance needs ability have fk constraints @ data size note.

all 'sharding' you're doing seems took swing @ premature optimizations future that's not expected if you're growing 10 million customers/record.

also, wouldn't classify situation 'big data', despite how term gets thrown around everywhere.

assuming table reasonable number of columns, less 30 columns of less 32 bytes each (char(32)), 10 million rows nothing mysql handle when indexed , given adequate memory hold innodb tables in memory (i'm assuming you're using innodb). i'm working tables factor of 10x size on aws xlarge rds instance without issues outside occassional time takes sql dump or time takes table alterations.

i'd combine various customer tables single table , take long @ queries hit it. run explain's on them see need indexes. keep fk constraints need them , makes sure have suitable covering indexes need.

i doubt need table partitioning performance on size of data you've indicated.