• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Methods for integrating cdbUsers table with stored user content
#1
Once in a while, we will provide support via email. I want to share that experience with everyone with the hope that others will benefit. When this happens, We'll leave out the person's name.

On to the question:
I've never really done nosql, was always able to query specific records against a user_id.
I want a quick way to save and query all of a user's records in this nosql way.

Do you see any limits or problems with creating a table for each user? and place all their data there?

I know for low numbers of user's thats not a big deal.
How about 10,000 tables?


The response:
There is always more than one way to model your data.

Method 1
You could use your cdbRecordID (primary key) for each user in the cdbUsers table as a linking key (foreign key) to records in another table.

cdbUsers table
Record 1: [a2ad3bc9-8ef8-4c36-9c4d-00d109600cd9] [Marco] [Polo]


someDataTable that contains data for all of your users
Record 34545 [0a43839f-ad5c-4089-a864-c6bfa10942be] [a2ad3bc9-8ef8-4c36-9c4d-00d109600cd9] [import] [data] [in] [these] [columns]


TableID:fbb12a0d-8189-4fb4-a906-12166445f54b
0a43839f-ad5c-4089-a864-c6bfa10942be


//Untested come code that should work:
//GET THE RECORD ID OF A PARTICULAR USER
local tOutputA, tRecordID
put cdb_query("email","=","marco@polo.com","cdbUsers","cloud","recordData") into tOutputA
put tOutputA["cdb"]["cdbRecordID"] into tRecordID


//USE THE cdbRecordID FROM THE FIRST QUERY TO FIND ALL RELATED DATA TO THIS USER
local tOutputA
put cdb_query("userForeignKey","=",tRecordID,"someDataTable","cloud","recordData") into tOutputA

//tOutputA will contain an array that for every record in the 'someDataTable' table that is related to your user. 
//For this to work, every commit to this table needs you to include the foreign key value in each record for every user.
//Since the user is probably authenticated, you can get this value one time during their session and use it everywhere you need.


Method 2
You can store the tableID in the cdbUsers table as a foreign key.
The optimization is that you can read on a single table and know that all the data is related to that particular user. You will not need to query a table to find related data. 

I am in favor of this model. The only negative is that a user can not create a table programmatically. It is something we intend to resolve. You could pre-create tables with a developer-signed app programmatically. If you get serious with this method, we can look at a longer-term solution to make this happen. 

You can connect as many tables as you like to a particular user. Just create the foreign key columns in the cdbUsers table to track the tableIDs.


Limitations
The free account “Mist” and entry level account “Rain" are running on a shared cloud cluster. They are great for developing concepts and low-data consumption apps. They will not work out for you if you plan to have 10k or more users. 

You will want to build a “Storm” account to customize the cluster just for your app. If you have 10k paying customers, our Storm plans are cheap. 
  Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)