1

So i have users and products each user can have multiple products which would make it 1 to many realationship. Now i am thinking about dynamically creating "username"_products collation for every user which would contain all products documents. Is it better to store everything in 1 collation called products which would have foreign key of user_id or is my design valid and preformance improving?

Also consider same thing for Mysql, is it better to have 1 table user and second table products with foreign key user_id or is it better to create seperate table of products called "username"_products? considering performance?

I know about indexes and that i should add indexes in any case if tables get big and if i go with solution number 1 only 2 tables/collations instead of multiple.

Community
  • 1
  • 1
Vjerci
  • 528
  • 2
  • 6
  • 16

1 Answers1

1

mongo (and nosql) goes against database normalization. you will have redundant data but thats in the spirit of things that nosql does offer -- such as speed and ultra-easy scalability.

basically you want to a simple and efficient find on an indexed key.

products = db.products.find("_id": user_id);

you don't want to simulate a join by conducting two queries.

user = db.users.find("_id": user_id);
products_id = user.products_id;
products = db.products.find("_id": products_id);

in fact, even doing something that would be utterly stupid in a RDBMS is acceptable in nosql. aka

products = db.products.find("department": "sales");

//?!wtf relationship info on a product table!?

because the above would require the following nosql nightmare:

sales_users = db.users.find("department": "sales");
myproductarray = [];
for each record in sales_users {
    products_id = record.products_id;
    products = db.products.find("_id": products_id);
    myproductarray.update(products)
}
return myproductarray;

further with mongo you can index any key including seemingly foolish ones like "department"

in my strong opinion, you are better putting off your tables and data structure until at least semi-finalizing your various use cases and your user interface. for example, include things like "department" in the product table if your use case dictates such a need.

Gabe Rainbow
  • 3,658
  • 4
  • 32
  • 42