2. PHPRunner General questions
  3. Value lists - best practice?
This topic is locked

Value lists - best practice?

 		1/14/2021 6:11:41 AM
PHPRunner General questions
mbintex author

Hi at all,

wanted to ask if any of you have a best practice regarding value lists in a multiuser, multilingual SAAS environment.

I see the following approaches:

  1. A table for every list consisting of the value and a user field for security purposes. On the one hand this approach makes it very clear, for which purpose a list/table is used and it can be space optimized. On the other hand you get so many tables in large solutions, that your organizational diagram gets very large and slow. Same for the user rights table entries.
  2. You define one central table with a field for "list type", value and user and in lookups you always have a where-clause on list-type, to get the wanted values. This might not be as space optimized as the first approach, but you only have one table in your diagram.
    Both approaches can be made multilingual and I see again two ways to do this:
  3. Add a field "language" to your value lists and then duplicate the entries for every language with the value localized in the respective language. The big pro of this approach is, that you can have as many languages as you like without changing the db structure. But it might get cluttered and difficult to find, for which language a value might be missing.
  4. Duplicate the value field for every language you might want to support. This is rather inflexible regarding the number of languages, but how often do you change that? Maintaining the translations is rather easy and straightforward.
    With the where-clause in lookups you can get the language values you need. But be careful. You need to save an ID field and not the language value to stay multilingual. This makes it more complicated for import and export as well as queries.

    Biggest problem in my eyes for a SAAS solution is the case, where you want to have some standard values for all AND the ability for every user to add custom values. With the standard dynamic security options you can't really have that. "User can see all and edit only their own data" shows them values they don´t want to have. So you would have to add to your where clause something like "where user=admin or user=customer" to achieve this.

    So what are you doing? Any other, better approach? What´s best practice?
admin 1/14/2021

Adding a language field to your tables with multilanguage content is the best idea. We cover it in this article:

https://xlinesoft.com/phprunner/docs/localizing_runner_applications.htm
In regards to SaaS approach - I would suggest having a separate database for each client. Less code, easier to maintain. See this:

https://xlinesoft.com/blog/2019/10/03/saas-application-design/
mbintex author 1/15/2021

thx
having a separate database for each client was our first approach, but now we have all customers/data in one centralized database:
-> number of databases is limited with most hosting agreements, size of database is not

-> updates are easier, although we have a technique to update the db structure automatically after successful login

-> adding a database for someone who only pays something like 29 Euros a year is too much work

-> adding a database for someone who stops working with the solution in the first days is somehow frustrating and leads to zombie databases

-> having demo accounts and customers in one database allows us to convert demos to contracts including data easily
admin 1/15/2021

I understand, that either route has its problems but your question was about best practices and the best practice is to have a separate database for each client.
How much work can it be to execute a script that creates the database? Or the one that drops it? Dropping a zombie database is really extremely simple but cleaning the data that the demo user created from all tables - sounds like a lot of work and prone to errors.
Not trying to criticize, just comparing these two options and the one with one database per client sounds so much easier to maintain.
In regards to the original question about a single lookup table versus multiple lookup tables. Both approaches are valid and it really depends. If you have dozens or hundreds of lookup tables then having a single lookup table is a plus.
fhumanes 1/15/2021

Hello:
I think you are interested in a solution of the type that I have intulated in some of my examples, like this one of the management of a Hotel https://fhumanes.com/hotel/ (login: admin and password: admin), Administrator option, "Super Catalog" and "Catalog" tables.

In this example, a new "Client" field would have to be expanded for auxiliary tables that were per Client.
You can see the whole example in my portal https://fhumanes.com/blog/gestor-de-proyectos/gestion-de-reservas/
Greetings,
fernando
mbintex author 1/16/2021

Will have a look at that. thx
mbintex author 1/16/2021



How much work can it be to execute a script that creates the database? Or the one that drops it? Dropping a zombie database is really extremely simple but cleaning the data that the demo user created from all tables - sounds like a lot of work and prone to errors.
Not trying to criticize, just comparing these two options and the one with one database per client sounds so much easier to maintain.


For these reasons I have written some backend functions like "deactivate user", "delete user with data", "activate user" etc. That works pretty well. Due to the GDPDU I have to delete data and accounts regularly, demo data also.
Our customers are very often single user, they have no idea of administration nor user privileges. Sometimes they were really fast in keeping themselves out of their own database or forgetting their logins (of course the registration email was wrong too).
Today I only offer personal databases if a customer has at least three users and pays a surcharge for "premium hosting".
I have no fear of critique :-)