2 added 262 characters in body
source | link

So, your design gets to save into your database two different kind of objects:

  • User object (which have always the fields).
  • Apps objects (which can have different fields). An app will belong to one user only.

A collection could or not me made as a different object, as is just a tag to group different apps. For the sake of argument, let's say there are no collections and users have just a list of applications.

While I think is achievable on MySQL, in MongoDB you'll have a greater flexibility in terms of the structure of app objects, and probably it will map more naturally your representation into the database, making the code simpler.

In MySQL you'll have problems dealing with different formats for different apps, but it is possible. Some ideas:

  • You can create an intermediate table with all the common info between all the objects (id, user_id, title, etc), then the type, so you can search for it on another table with only the non-common fields for that format (e.g. file_name and file_size for files). You'll need to create a different table for each different format. If both tables are indexed by app_id (Primary key), it will be fast enough, as accessing a table by an indexed value is fast.
  • You can encode the data in some format and store standardized. E.g., encode the non-common data in JSON as a string and store it on a VARCHAR field. Be careful with the size of that field so you don't run out of space. The format can be complex (JSON) or simple (just values separated by commas)
  • You can create different "generic" fields, something like int1, int2, str1, str2, and define that str1 for an app type is "file_name" while for a different type could be "location".

On MongoDB, it could be as simple as just using two MongoDB collections, one for the users and another one for the apps. Assuming some sort of limit (which is not the case, as you described, but just for saying), you could even store the apps inside the user object, as a list. Storing and retrieving the data is more natural, as you can store any kind of object, no matter which are the fields. You can search by user_id to get all the apps that belongs to a user. On MongoDB you loose anyway the possibility of doing join queries, but in this case I think the basic queries will be retrieving the user and retrieving the apps related to the user. If you plan to do a lot of stuff like "give me the users who have more than two collections with three applications or less on each", you'll have to generate it not as a join query, but as a process in code, and will be less natural than in a relational database and can take more time to process. If you want to search for parameters (e.g. give me all the apps that belong to a particular user; give me all the apps that are of type X), that's quite easy on MongoDB and don't need to use joins.

I'm not sure about the support of MongoDB on Rails. I have use it in Python and JavaScript.

EDIT: Added comment about time when accessing two tables and another MySQL option

So, your design gets to save into your database two different kind of objects:

  • User object (which have always the fields).
  • Apps objects (which can have different fields). An app will belong to one user only.

A collection could or not me made as a different object, as is just a tag to group different apps. For the sake of argument, let's say there are no collections and users have just a list of applications.

While I think is achievable on MySQL, in MongoDB you'll have a greater flexibility in terms of the structure of app objects, and probably it will map more naturally your representation into the database, making the code simpler.

In MySQL you'll have problems dealing with different formats for different apps, but it is possible. Some ideas:

  • You can create an intermediate table with all the common info between all the objects (id, user_id, title, etc), then the type, so you can search for it on another table with only the non-common fields for that format (e.g. file_name and file_size for files). You'll need to create a different table for each different format.
  • You can encode the data in some format and store standardized. E.g., encode the non-common data in JSON as a string and store it on a VARCHAR field. Be careful with the size of that field so you don't run out of space.

On MongoDB, it could be as simple as just using two MongoDB collections, one for the users and another one for the apps. Assuming some sort of limit (which is not the case, as you described, but just for saying), you could even store the apps inside the user object, as a list. Storing and retrieving the data is more natural, as you can store any kind of object, no matter which are the fields. You can search by user_id to get all the apps that belongs to a user. On MongoDB you loose anyway the possibility of doing join queries, but in this case I think the basic queries will be retrieving the user and retrieving the apps related to the user. If you plan to do a lot of stuff like "give me the users who have more than two collections with three applications or less on each", you'll have to generate it not as a join query, but as a process in code, and will be less natural than in a relational database and can take more time to process. If you want to search for parameters (e.g. give me all the apps that belong to a particular user; give me all the apps that are of type X), that's quite easy on MongoDB and don't need to use joins.

I'm not sure about the support of MongoDB on Rails. I have use it in Python and JavaScript.

So, your design gets to save into your database two different kind of objects:

  • User object (which have always the fields).
  • Apps objects (which can have different fields). An app will belong to one user only.

A collection could or not me made as a different object, as is just a tag to group different apps. For the sake of argument, let's say there are no collections and users have just a list of applications.

While I think is achievable on MySQL, in MongoDB you'll have a greater flexibility in terms of the structure of app objects, and probably it will map more naturally your representation into the database, making the code simpler.

In MySQL you'll have problems dealing with different formats for different apps, but it is possible. Some ideas:

  • You can create an intermediate table with all the common info between all the objects (id, user_id, title, etc), then the type, so you can search for it on another table with only the non-common fields for that format (e.g. file_name and file_size for files). You'll need to create a different table for each different format. If both tables are indexed by app_id (Primary key), it will be fast enough, as accessing a table by an indexed value is fast.
  • You can encode the data in some format and store standardized. E.g., encode the non-common data in JSON as a string and store it on a VARCHAR field. Be careful with the size of that field so you don't run out of space. The format can be complex (JSON) or simple (just values separated by commas)
  • You can create different "generic" fields, something like int1, int2, str1, str2, and define that str1 for an app type is "file_name" while for a different type could be "location".

On MongoDB, it could be as simple as just using two MongoDB collections, one for the users and another one for the apps. Assuming some sort of limit (which is not the case, as you described, but just for saying), you could even store the apps inside the user object, as a list. Storing and retrieving the data is more natural, as you can store any kind of object, no matter which are the fields. You can search by user_id to get all the apps that belongs to a user. On MongoDB you loose anyway the possibility of doing join queries, but in this case I think the basic queries will be retrieving the user and retrieving the apps related to the user. If you plan to do a lot of stuff like "give me the users who have more than two collections with three applications or less on each", you'll have to generate it not as a join query, but as a process in code, and will be less natural than in a relational database and can take more time to process. If you want to search for parameters (e.g. give me all the apps that belong to a particular user; give me all the apps that are of type X), that's quite easy on MongoDB and don't need to use joins.

I'm not sure about the support of MongoDB on Rails. I have use it in Python and JavaScript.

EDIT: Added comment about time when accessing two tables and another MySQL option

1
source | link

So, your design gets to save into your database two different kind of objects:

  • User object (which have always the fields).
  • Apps objects (which can have different fields). An app will belong to one user only.

A collection could or not me made as a different object, as is just a tag to group different apps. For the sake of argument, let's say there are no collections and users have just a list of applications.

While I think is achievable on MySQL, in MongoDB you'll have a greater flexibility in terms of the structure of app objects, and probably it will map more naturally your representation into the database, making the code simpler.

In MySQL you'll have problems dealing with different formats for different apps, but it is possible. Some ideas:

  • You can create an intermediate table with all the common info between all the objects (id, user_id, title, etc), then the type, so you can search for it on another table with only the non-common fields for that format (e.g. file_name and file_size for files). You'll need to create a different table for each different format.
  • You can encode the data in some format and store standardized. E.g., encode the non-common data in JSON as a string and store it on a VARCHAR field. Be careful with the size of that field so you don't run out of space.

On MongoDB, it could be as simple as just using two MongoDB collections, one for the users and another one for the apps. Assuming some sort of limit (which is not the case, as you described, but just for saying), you could even store the apps inside the user object, as a list. Storing and retrieving the data is more natural, as you can store any kind of object, no matter which are the fields. You can search by user_id to get all the apps that belongs to a user. On MongoDB you loose anyway the possibility of doing join queries, but in this case I think the basic queries will be retrieving the user and retrieving the apps related to the user. If you plan to do a lot of stuff like "give me the users who have more than two collections with three applications or less on each", you'll have to generate it not as a join query, but as a process in code, and will be less natural than in a relational database and can take more time to process. If you want to search for parameters (e.g. give me all the apps that belong to a particular user; give me all the apps that are of type X), that's quite easy on MongoDB and don't need to use joins.

I'm not sure about the support of MongoDB on Rails. I have use it in Python and JavaScript.