Are you doing this project on your own?
If it is going to be as big as you say it is, surely you have a team that included a DBA?
Hello - at this stage no we don't have a DB Admin - can't afford it yet, so would like to move towards having a good platform - so we don't give whoever gets hired long term a Heart attack.
Ive made some decisions:
We have reference data - that will be stored separately to the main functions.
Id also like to implement Horizontal partitioning - perhaps with a replicated schema (So there are nice fast little units all over the place - Customer 1 - Server 1 - Customer 10000 - Server 2 Perhaps) - Ill need to get a schema defined to help with determining the partition size.
I was thinking replication of the login table (LoginID,Email/Username, Password, CustomerID) might be useful rather than trying to split the table by Email (As that will change) - Good/Bad idea? Least say 10 million logins (Possibly alot more - but that could be the DB Admins job).
Customer record - Per Partition.
Associated Child Tables - Could be up to 10 million transaction records (I would expect this to take longer than a year to tally up and would be on the larger scale of the business units involved) though I expect this would take a while (Logs would be elsewhere)
Is there anything I would need to look out for when moving the Customer records between partitions (Should I simply start with smaller partitions - perhaps split on the same physical server - as a scale up was needed simply move those elements)
With Horizontal Partitioning would it be a good idea to replicate the partition.
For the most part these developments will be token to start with but will allow the code to be in a very scalable structure for later on - when it will be needed.
If you're planning splitting on the same physical server, you'll need to consider - how you will distribute the partitioned data across different IO channels - to maximise performance
I think initially the performance lost by such a design would be worth it to save on future development hassles. Also if designed well - thinking Lookup system: Then partitioned Locations each partitioned location would be queried separately, also the lookup query could have a copy of the relevant data (Search Field, Display field, something else interesting) to remove the need for queries to the partitions on lookups accross locations. Then selection of a particular record would be distinct to the connection of the partitioned server (Say edit or View detail)
Public view for transactions, would probably be a series of constructed views/tables - containing current available numbers (Based on last processing schedule) - which would combine particular information into much more available sets with only the information needed for those business processes.
Please correct me if this is dumb for some reason?
The database is the lowest layer, the foundation, of your application.
You need the assistance of a DBA at the start.
You will also need DBA assistance at the end.
Typically it is in the middle of a project when your need for DBA assistance ebbs.
If it's not practically useful, then it's practically useless.