Android Question A SQLite db for each user on a central server?

[LucaMs]

I have a question difficult to explain, because of my poor english and complexity. (Indeed, they would be at least two)
I entrust myself to your intelligence to understand anyway.

Given that I know very little PHP (but enough to use copy and paste ) ...

A central DB server, such as a warehouse, many clients through which some employees consult and update the warehouse.

This is a "normal" case.

The case in which, instead, each user of an App that uses a SQLite database ...
how to structure the database server?

Create a unique db on the server and, on every table, in every record, an identifier for the user so that he can only work on his own data?

Or create a db for each user? (hoping that they are millions!)

In the first case, the data processing might be too expensive.
In the second, how many problems with the host site?

 

[KMatle]

Question: Why do you want to use a local db? I guess you want to have the data on the device to speed it up (like items to sell in the warehouse).

If the users are online all the time (which means they have connectivity all the time and no offline times) I would prefer a server solution and not having local databases at all. This solution is very easy.

 

[nwhitfield]

If you're having all the data on a server, just have one database; you don't want the overhead of your script opening and closing lots of connections, and maintaining pointers to different databases - and of course, there may be something that would be duplicated anyway. If you're using MySQLi (and some other database extensions), a pool of connections to the database will be maintained, if they're all the same database.

Internally, you might use something simple, like an INT, to indicate the user id in your database, but you probably don't want to expose that to the user, because it might make it easier for people to fiddle and work out how to access someone else's data.

So, when you app connects to the server with whatever credentials it supplies (email and a password, say), the server will validate that, and find out from one of its tables what the internal user id is. It can then create a unique key (a hash, say, based on the time, the email address, and the user id), and store that in a table, as well as passing it back to the client app.

Make the client app pass that hash each time it sends a request, rather than an id or username that someone can easily spot if they're monitoring traffic; the database can look it up, and easily work out the correct id, which will be used in every single query, so that the user can't change anyone else's data, so virtually every query will have "WHERE memberid = X" in it.

There might be some information you want to cache locally; I do that in one of my apps, for example. Information about who's online, or full info for all members, is never cached, but the preview info (name, location, summary) is cached (and deleted if flagged on the server as changed, or not used for a long time). So, if you request someone's info, after the first time, the summary will appear almost instantly from the local cache, while you wait for the full info from the server.

You might want to think about when this approach might work for you. For example, the database in the warehouse might be the most up to date with information on stock and so forth. But it might be useful to have a local copy of all the products available on each device, without stock levels, which allows people to search on their device, even without a connection, and see if you may have something, and the product number.

 

[LucaMs]

First of all, thank you for your answers.

There are two possibilities:
1) I have not understood the answer
2) I have not explained well.

The most likely of these is: both

In fact, I have not explained some basics.


The user's app has some tables editable by the user and some of his friends.

So the Hash key could be a group key group, ok.

but then ... should I have something like this:

TableX
---------
GroupKey
Field1
Field2
...

?

Or many identical DBs for each group?

The first solution, which is more "clean", would not result in a too slow queries?

Because tables (in my case they are only a dozen) will contain thousands of records for each group and whether the groups were tens of thousands, each table may need to contain many millions of records.

 

[Reviewnow]

Your Table Relationships and schema would have to be first correctly designed

The object of the below schema
A: Have a client Table
B: Have a User Table (User is Member of Client and also belongs to X Groupkey)
C: Have a Group Table (List of all Groups)
D: Have a Product / Additional Tables that each item belongs to X Group

Every Member of X group can now modify each product that belongs to X Group



+--------------------------------------------------------------------------------------------------------------+
Object name :Clients
Description :Client Master Table

attribute description
--------- -----------
ClientID Master ClientID
Name Client / Company Name
Address Client Address


+--------------------------------------------------------------------------------------------------------------+
Object name :Groups
Description : Master Group Table

attribute description
--------- -----------
PK GroupKey
Description Key Description


+--------------------------------------------------------------------------------------------------------------+
Object name :Users
Description :Master User Table

attribute description
--------- -----------
PK UserID
PK ClientID This User is an Employee / Member of Client
PK GroupKey This User Belongs to this GroupKey
Name User Full Name
Address User Address Field
UserName User Name Field
Password Password Field
FK_ClientID_1 Related To Clients Table
FK_GroupKey_1 User Belongs to this Group


+--------------------------------------------------------------------------------------------------------------+
Object name roducts
Description roducts Table

attribute description
--------- -----------
ProductID All Products for Group
GroupKey Products In Group
ProductName Product Name
FK_GroupKey_1 Related to Groups Table

 

[LucaMs]

Many many thanks ReviewNow, but i know relationships (however, it is very usefull for all of us )

But my question is about the timing!

Hoping to have tens of thousands of users, the tables become huge and moreover related!

I wonder whether it would be better to use a db for each group.

 

[Reviewnow]

It would depend on the number of groups if its only a handful it could be manageable to create a db for each group. but if its hundreds of groups
the management would be difficult to say the least, you would have to create individual applications to manage each group and would be at least very time consuming. then to distribute the warehouse to each group is another task.

It would be best to have a central database and design the schema to be manageable across all groups

you could always create temporary tables from the master tables for each group then query and release the temporary table when not in use

it is then manageable.

Good Luck

 

[LucaMs]

The groups should be just of 2-3 persons.

Each user should insert about 10-100 new rows per day in a table (related with...)

Assume that my AWESOME app is downloaded by a million users.
Assume they are 700,000 groups.

50 new rows per day for each user are 50,000,000 per day added in one table?

 

[Reviewnow]

Honestly I have never had to deal with that many records, I am not sure how to lead you in the right direction for that many records
I am sure there are some limitations on the number of records/Size a database can have, you will have to plan for storage growth and so on.
and maybe even some clustering servers, you will have to allow for query plans and so on, how long will it take to query your trillions of records?
will the limitation's and amount of records make your application unusable lots of considerations I wish you luck...

 

[nwhitfield]

If you are anticipating that many people, then in some ways, it is likely to be better to have a single database (or one that appears as a single database), rather than lots of others. Depending on the sorts of queries being done, a database engine is going to cache some information in memory, and if there is common information across all users, that is likely to be cached, as long as it's the same database.

If each user has their own database, then the server will need lots more memory to cache it - and consequently a bigger server will be needed. And as one user finishes a query, and another starts theirs, if the server has to swap out one load of cache for another, as it connects to a different database, you'll probably see a performance hit. What about the warehouse, having to query all the information from all the different databases?

Also, if you have to set up a new database, for a new group you need to think about how that is handled; can you do that programatically from your back end when someone creates one? Can you set up all the permissions in your code? Would allowing your server side code to create new databases on the fly potentially compromise the security of your system? What happens if, in future, some mechanism is needed for people to be a part of more than one group? Or to move from one group to another?

I really think keeping it all in one is going to be simpler, and more flexible, and probably more secure too.

 

[LucaMs]

"Can you set up all the permissions in your code"

Although this was a question: what hosts allow.

It is clear that I joke when I say I predict a million users, but there are apps that also have a higher number.

I take the example of an app that manages the household accounts.

Each family member must have access to the informations.

They should have its own db centralized online.

But if I want to provide them their informations?
And if these families are hundreds of thousands?

I'll think calmly, reading your valuable advices.


Thank you so much

 

[KMatle]

At work I did some "apps" with over 200Mio. rows. It's not about the amount of rows (or users, groups, items, etc.) it is about how you design it and how much space this needs.

For private uses I have a homepage with a package with 5 Databases/2 GB each. To fill 2 GB you will need a lot of data.

On the other hand you will need to know how many app users will use your app at the same time. I don't have an answer for that (did not find any ressources here or in the www).

 

[nwhitfield]

If you're running or managing your own server, then yes, your host will allow whatever you want - and if you're anywhere near serious about having that many users, you need a hosting service that lets you configure the database and permissions exactly how you want, so you could have parts of your script able to create databases, and set permissions appropriately. However, I'd caution that you'd likely be mad to set things up that way, because of the security risks.

Structure your queries properly, and you can manage everything in one database; servers like MySQL don't have specific limit on the number of records, and will be able to cope.

If these are families (rather than warehouse workers, as in your earlier example) there is even more reason to keep it in one database - families change; people marry, divorce, remarry. They don't have straightforward households any more. A husband might share some accounts and money with his first wife and children, and others with his second, and you need to be flexible enough to cope with that. A database per family will just complicate things even more.

As Klaus says, design the database properly. In particular, think about the queries you make, and the joins or relationships between them. Do that at the start; it will probably change as you add new features, but if you don't plan carefully, you end up with either mind bending queries (anyone for a LEFT OUTER JOIN ?) or having to do stuff in your web app code (eg PHP) that you could have done in the database if you'd planned properly - and that will affect performance.