2

I'll be handling 300,000+ users. Each user (after logging in) will be able to create lets say "folders", in each folder, users will create "name=value,name2=value2,name3=value3" entry pairs. Each name won't be more than 260 chars and each value won't be more than 1024 bytes. All readable characters.

My ideas for possible approaches:

  1. Create tables called userentries_[first letter of username] so there will be 26 tables, each table will have entries and user ID. So when I want to retrieve user data, I'll do

    select * from userentries_[firstletter] where userid= $UID"
    
  2. Create a single table to hold all information.

  3. Create a table called userdata, here I'll have UID and FileName. So when user wants to retrieve data, I'll read database to find the name of file, then open FileName in PHP, create an XML/JSON response and client (which will be android/ios/desktop app) process it.

Which one is the best and most optimized (speed/performance vise) ?

P.S. name=value pairs etc. won't be searchable, even if user supposed to search through them, it will be in client-side, definitely not on DB

  • 1
    In short: create a single logical table, partition it by whatever you see fit. Usually dates are a better fit than names: old partitions are rarely touched. – 9000 Dec 11 '14 at 3:19
  • 2
    300,000 users = Huge Data? – Jim G. Dec 11 '14 at 5:47
  • How many concurrent users at once? Do you have a hardware budget? – kevinsky Dec 11 '14 at 17:04
  • @JimG. I should've said minimum. – JustACPPFan Dec 20 '14 at 21:52
  • @kevinsky: at least 100k concurrent. Yes, I'll be using Amazon cloud so I can add/remove services/servers needed as users growing – JustACPPFan Dec 20 '14 at 21:53
2

You might use three total tables (adjust table/column names and SQL dialect to taste).

The first is the user table, which contains the master list of user_id's.

The second is the folder table, that will contain a list of folders for users.

|  folders         |
|------------------|
| id (int)         |
| user_id (int)    |
| name (varchar)   |

And a third for the name/value pairs

|  user_data       |
|------------------|
| id (int)         |
| folder_id (int)  |
| name (varchar)   |
| value (varchar)  |

To query for the name/values in a specific folder then, you'd do something like:

select name, value from user_data where folder_id = ?

To query all data for a user it can be something like:

select user_data.name, user_data.value 
  from folders
  join user_data on (user_data.folder_id = folders.id) 
  where folders.user_id = ?

Alternatively, denormalize the user_data table slightly and add the user_id in there as well. That will simplify queries for all of the user's data, but you'll have to manually keep the keys in sync.

Properly indexed, this will work fine for large data sets.

  • 1
    I appear to have accidentally down-voted this question. Could you edit it so that I can up-vote as I intended? – Jules Dec 11 '14 at 8:32
  • @Jules sure, no problem :-) – GrandmasterB Dec 11 '14 at 16:22
1

The question can be reworded as "I have the canonical perfect application for a nosql key-value store, what should I do?". Yes, this can be done with sql, but a nosql implementation is likely to be more efficient.

Look at systems like riak, apache cassandra, berkeley db or memcachedb, or if you're planning on deploying to amazon web services dynamodb. Or perhaps consider a more traditional database with either memcache or redis as a cacheing layer.

  • Thank you for this suggestion, I've never heard of DynamoDB before. Can you explain a little bit about it? I mean, I still need user,pass,name,email,phone,address,credit card, etc. info then every user after login, eventually using UI application, will need to pull some data which can be from a file. You mean I have to use DynamoDB for pulling these file type data or for entire system including name,pass,etc. ? – JustACPPFan Dec 20 '14 at 21:51
0

You may want to consider not using SQL at all, but use something like Azure table storage.

Troy Hunt has an interesting article on its performance on huge datasets: http://www.troyhunt.com/2014/12/applied-azure-infographic-of-how-have-i.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.