0

I'm working on creating an application best described as a CRM. There is a relatively complex table structure, and I'm thinking about allowing users to do a fair bit of customization (adding fields and the like). One concern is that I will be reaching a certain level of scale almost immediately. We have about 50,000 individual users who will be coming online within about nine months of launch. So I want to build to last.

I'm thinking about two and maybe even three options.

  1. One table set with a userID column on everything and with a custom attributes table created by creating a table which indexes custom attributes, then another table which has their values, which can then be joined to the existing contact records for the user. -- From what I've read, this seems like the right option, but I keep feeling like it's not. It seems like once these tables start reaching the millions of records searching for just one users records in every query is going to become a database hog.

  2. For each user account recreate the table set, preened with a unique identifier (the userID for example.) Then rather than using a WHERE userID=? everywhere I can use a FROM ?_contacts. For attributes I could then have a custom attributes table where users could add additional columns for custom attributes. -- This feels like the simplest way to go, though, of course when I decide to change the database structure there would be a migration from hell.

  3. The third option, which I'm pretty confident is wrong, but for that reason alone I can not rule out, is that a new database should be created for each user with all the requisite tables.

Am I crazy? Is option one really the best?

cfkane
  • 643
  • 1
  • 6
  • 16
  • 50,000 users. How many companies? – Mike Sherrill 'Cat Recall' Dec 02 '13 at 13:00
  • This answer could be useful - http://stackoverflow.com/a/876459/1048425 – GarethD Dec 02 '13 at 13:29
  • @MikeSherrill'Catcall' I hate it when people are all sketchy about the details of their IP, but in this case I kind of have to. The net result is that while the app is like a CRM in structure it's not one, so the 50k users won't have shared data within their organiztions. – cfkane Dec 02 '13 at 17:40

1 Answers1

1

The first method is the best. Create individual userId's and then you can assign specific roles to them. A database retrieval time indeed depends on the number of records too. But, there is a trade-off where you can write efficient sql queries to fetch data. Well, according to this site, you will probably won't run out of memory or run into concurrency issues, because with a good server, the performance ought to be good, provided that you are efficient in writing queries.

If you recreate table sets, you will just end up creating lots of tables and can make the indexing slow which is a bad practice. Whereas if you opt of relational database scheme rather than an ordinary database scheme, and normalize the database and datatables for improving efficiency.

Creating a new database for each and every user, just sums up the complexity from both the above statements resulting in a shabby and disorganized database access. Because, if you decide to run individual instances of databases for every single user, you would just end up consuming your servers physical resources like RAM and CPU usage which will affect the service quality of all the other users.

Take up option 1. Assign separate userIds and assign them roles and privileges where needed. That is more efficient than the other two methods.

user3041913
  • 31
  • 3