Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The problem you describe is commonly known as "multi-tenant architecture". There are no right or wrong answers - but there are a whole bunch of trade-offs. </p> <p>Your design - table namespace per client - has the benefits you mention. Each client has their own fenced-off set of tables, so it's hard for any one client to affect another's data, and you can apply different versions to different clients. Drawbacks are pretty horrible though - every time you change the application, you end up changing hundreds of clients' tables. If you have common data - currencies, countries of the world, sizes - you have to either duplicate those across clients (JOHNDOE_COUNTRIES) or have a single one which breaks the "all of John Doe's tables are called JOHNDOE_" consistency. A single bug - especially a performance issue in your queries - scales to the number of clients you have, and has to be fixed for every single client. In your application code, you have to spend time changing the name of each database table, which makes many ORM solutions harder to work with.</p> <p>The alternative option mentioned here - database per client - is far more elegant. You keep the separation between clients, you can put different clients on different physical hardware to manage performance/scalability, you don't have to mangle the table names in your application code, and your build/deployment scripts are far simpler. However, you still have the whole "make the same code change once for every client" problem. It becomes a problem very, very quickly - if your build/deploy script takes 10 minutes, by the time you have 200 clients, it would take all day to upgrade your application. </p> <p>You can, of course, bake the client_id into the database schema - have CLIENT_ID as a column in CLIENTS, ORDERS etc. This means that a bug in your code means one client can affect another client's data - but that possibility still exists in the other scenarios. The code which mangles the table names in option one could contain a bug which selects the wrong client prefix, for instance. </p> <p>However, a single large database with lots client_id to identify which client "owns" the data is <em>much</em> easier to manage - a single build/deploy script, fixing and testing bugs can happen just once, new features are rolled out just the once. This cost saving is potentially huge.</p> <p>So, my recommendation would be to have a single database with CLIENT_ID to begin with, but bake in the option to select a different database for each client so you can scale to multiple databases in the future. I would not use a different table for each client.</p>
    singulars
    1. This table or related slice is empty.
    1. POData-centric application: separate database tables for separate organizations a good idea?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USNeville Kuyt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHello. You mention that an application upgrade implies having to change the tables of every single client, thus requiring quite a lot of code. Instead, what I was thinking, is why not have the client upgrade the database to the next version. So when the client chooses to upgrade, a new database is created, tables are migrated, and then the old database is deleted. This way the client has control over the version number and takes their own time to do the upgrade.
      singulars
      1. PO
      1. USJason Posit
    2. COThe situation is different of course if there is a bug rather than a feature enhancement, which means an upgrade must take place immediately. However, given I can have my own databse for testing purposes, this should make bugs less likely. So for each different database schema I can have a test database which looks exactly the same.
      singulars
      1. PO
      1. USJason Posit
    3. COWhat I would like to do is set up a script which generates the user interface from the data present in the database schema definition files and push it to each client according to their own needs.
      singulars
      1. PO
      1. USJason Posit
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload