-2

I am a fresh frontend web programmer. and I recently programmed few types of web based mobile applications(means originally developed on web using angularJS and I have used a tool to create .apk file) basically for myself .these apps are including " Creating Shopping List" , "Price Comparison" , "Things To Do" and "Motor Pump Alignment Readings Calculator" . But these apps are limited to myself .But I want that any other user should be able to use it.Problem is that My webhosting plan allow only 5 databases .So I can not create a new database for every new user. So please help me and suggest me that how every users data can be stored seperately .
An Example: When i have to create a new shopping list, then it should be in a new table. For just myself , it is not a problem but suppose there are 200 users (e.g. my friends and family member) , then how i will manage it...?

Here is a very short and simple code for concept clearing that how a new shopping list is create. 
ِHTML: <input type="text" ng-model="newItem" /><button ng-click="add()">Add</button>

JS:

var app = angular.module("app",[]); app.controller("myCtrl",function($scope,$http) { $http.post( "inser.php", {'listItem':$scope.newItem} ).then(function(response) { $scope.showdata = response.data; }) });

PHP: 

 $connect = new mysqli("host","user","pass","db"); $data = json_decode(file_get_contents("php://input")); $list_item = $data->listItem; $insert = $connect->query("insert into TableName (coloumn) values ('$value')");

In this case , a database and a table is already assigned to query. and it is easy but for dynamic user experience , I am unable to get any idea.

Improve this question

3 Answers 3

Reset to default
3

The What

It's the application that grows from single-user to multi-user. The database needs to become more sophisticated to handle that. But you still need only one database per one web application.

You need to identify user ("[email protected]", "[email protected]").

Predict that you'll need to authenticate user (password?) and to authorize access (admin? power user? standard user?).

The How

More columns. Primary keys. Foreign keys.

For example you've already hit the wall with this "When i have to create a new shopping list, then it should be in a new table." This approach doesn't scale.

You do:

CREATE TABLE shoplist ( item VARCHAR );

Think about this:

CREATE TABLE shoplist ( shoplist_id NUMBER PRIMARY KEY, item VARCHAR );

Now think about this:

CREATE TABLE shoplist ( shoplist_id NUMBER PRIMARY KEY, item VARCHAR, item_quantity NUMBER );

Now think about this:

CREATE TABLE shoplist ( shoplist_id NUMBER PRIMARY KEY, owner_login VARCHAR, item VARCHAR, item_quantity NUMBER ); -- one table holds many shoplists for many users, how to show them: SELECT item, item_quantity FROM shoplist WHERE owner_login = '[email protected]' ORDER BY shoplist_id;

Now think about this:

CREATE TABLE shoplist ( shoplist_id NUMBER PRIMARY KEY, owner_login VARCHAR, // declare it a foreign key - the value always in (SELECT login FROM users) item VARCHAR, item_quantity NUMBER ); CREATE TABLE users ( login VARCHAR PRIMARY KEY, salt VARCHAR, password_hash VARCHAR, is_admin VARCHAR );
Improve this answer
0

You only need one database on the back-end.

Your users authenticate to the web server, not the database. The web server connects to the database, not a user.

SUGGESTION:

  1. Create a DB table for "users", and another for "groups".

  2. Map users to one or more groups, and map "access privileges" to your groups.

  3. Modify your server-side code to do a SQL "join" to map user::group::access before you make the actual select, update or delete.

    If the join returns 0 rows for your authenticated user ... then that user isn't granted access to make the following "insert".

  4. NEVER try to make SQL or do the DB "connect" from the client.

    ALWAYS have the client invoke server-side code that talks to the database on their behalf.

    Which, as it happens, is EXACTLY as you've already done in the PHP snippet above. All you need to do is modify your PHP to check for access, before it does the "insert".

Improve this answer
3
  • I also was thinking about a few of the things that you mentioned above. Thank you . but if you explain the No. 4 "Never try make SQL or do the DB "connect" from the client" , then it will be a bit more better. Commented Mar 11, 2018 at 4:41
  • You don't want to open another port in your server's firewall if don't have to. You want to reduce the "attack surface" by CLOSING as many server ports as possible. You CERTAINLY don't want to expose your database to any hacker on the Internet. Among (many other!) reasons. For example, by default, mySQL is configured to allow ONLY local connections (reject any clients not connecting from 127.0.0.1 localhost address). Commented Mar 11, 2018 at 6:13
  • This answer isn't wrong … but what does it have to do with the question? The question seems to be about data modelling, yet this answer describes access control mechanisms? Commented Mar 11, 2018 at 9:29
0

The answer is that you shouldnt create a new table. Think not 200 users, but a million. A good database on a good server has no problem handling a table with shopping lists for a million users, that’s what databases are designed to do. Handling a million tables, now that is a problem.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.