Question 58195 in Mysql Question posted in
The official MySQL documentation can be found here.

Do dynamic table namespaces exist? – Mysql

I’m not sure if "dynamic table namespaces" is the correct way to describe what I’m looking for.

I am developing a web app which will be used by an indefinite amount of clients. Nearly every table has a clientId to associate the data with a client.

Client data will never be shared across clients. Only a few tables out of 30+ do not scope data by a clientId.

Wouldn’t it be simply if, instead of using clientIds on all the tables, then having to handle scoping all of the data in my api to whatever clientId is currently active, I could instead have some sort of dynamic namespace, so that my api can say "I’m currently working on EXAMPLE_CLIENT_NAME", and the DB will only get/insert data into every table based on that.

For instance:

db.init("EXAMPLE_CLIENT_NAME")
db.getAllProducts() //Queries product table based on EXAMPLE_CLIENT_NAME namespace
db.getAllUsers() //Queries user table based on EXAMPLE_CLIENT_NAME namespace
db.insertUser(data) //Inserts a user based on EXAMPLE_CLIENT_NAME namespace
db.getAllCountries() //Shared table across all clients. No scoping based on clientID happens

It’s almost like have a separate database for each client, but being able to have some tables shared, and not having to spin up a separate DB for every single client, as the number can be infinite.

I think this would greatly simplify my api.

Does this exist?

Tags:

2

Answers


  1. 0

    No such thing exists in MySQL. You can implement one in your API though.

    Login or Signup to reply.
  2. 0

    You could run an initial script that created a bunch of client specific temporary tables. I’d consider something like a table naming convention as a substitute namespace. I am assuming that although there might be an unlimited number of clients, their temporary tables would not hang around indefinitely, so you keep the number of tables sensible (been a while since I’ve done MySQL but I’d expect anything <20k temporary tables to be okay). I’d leave the COMMON tables alone but probably name them as such so it’s easy to spot in the code.

    Assuming clientid is indexed on all these tables, then creating all these temp tables will be pretty quick and as the dataset will be vastly reduced I’d expect a significant boost in query performance for any non indexed searches.

    I expect though you will need quite a bit of ram available.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search