In my application, users (and all data) is grouped by company. So, for example, every customer in the database has a company assigned to it, as does every product etc. The user can log in with their company ID and they can only access the information from that company, however this feels like the wrong way to do things (having all data in the same tables with only the company ID separating them).
My two problems are:
- Say someone from company 1 makes 2 products, with product ID 1 and 2, then someone from company 2 makes a product, it will have ID 3 unless I make a (what I believe is unnecessary) query to count the number of products and add 1.
- I just don't feel it's secure to have all the data this close together, and it's going to cause huge bloat of the database when/if we have 100+ companies all with their 100+ products and customers in there.
What is the best way to go about this, or is this the best practice and I'm just seeing ghosts?