My proof-of-concept service has been in production for 2 years now and the db size is 125MB (1 company, 2 inspectors). As I design the 'real' online service for multiple inspection companies, my big concern is for performance, scalability and manageability. Security and privacy are a concern but based on posts, this appears doable regardless the path.
Regardless the levels of services i plan to offer, there's only one database schema in mind at this time.
I expect high frequency concurrent transactions
+ up to 8 inspections a day will be created per company (initial inserts and updates)
+ inspections are conducted online/real-time via wireless device. client is web browser. (inserts)
+ average of 25 digital images are stored per inspection. (inserts)
+ related parties view a completed inspection + images average of 5 times per day. (reads)
+ after 30 days, an inspection may never be viewed again
+ inspection companies will have a variety of canned reports that lend insight into their business over time. (read)
+ inspection companies maintain a list of real estate agent (contact info) which is not shared across companies
The responses are most helpful and thanks to muji_mu for helping me label the question as "single" vs. "multi" tenant. I'm learning how to think about this and can read the MSDN article and Google.
If I go with a multi-tenant model, is it a difficult task to move to the single-tenant model if future success requires it?
Does SQL 2005 or 2008 allow for partitioning across servers? my ignorance complete here.
Mort Strom