10-27-2021, 11:05 PM
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.
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.