0

I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations. We are making a change to the way our site works - from now on Clients will upload / add their own data, which will only be accessible to them. We store the Client data in the client table, each client has an id. There are 3 simple ways I can think of to design this, but I want the most efficient, as some calls will have to select 10,000+ records for that Client (in the case of a large world map of events, for example). So which if these ways should I use to ensure that all data a Client "owns" is recalled with maximum efficiency?

  1. Many to many relation to each type of entity. -

client.id ... | client_id, image_id | image.id ...

(and create a many to many table for each of the dozen entities involved). V simple, but could get in a tangle as we add more and more entity types, many of which have relations of their own. New many to many tables will need to be added regularly

  1. Polymorphic association one table to associate client_id with any type of entity. Most efficient in terms of layout but slow to find all entities belonging to one client.

client.id ... | client_id, entity_type, entity_id | image.id ...

  1. Add owner (client_id) column directly to each entity table

client_id, image_id, image_name etc... (all in the image table, same for each table)

Is there any reason not to add the client ID directly? It would mean storing a few more integers than the other methods but would be extremely fast to select all images 'owned by' a client (could be tens-of-thousands+)

  • 1
    Unless several clients can own the same image, your alternative 3 looks fine. For a 1-n relationship that is the one typically used. – Lennart Jun 27 at 11:44
  • 1
    It looks like option 3 would allow for easy implementation of Row Level Security (RLS). It may also be easy to apply RLS-like techniques within the databases that don't support RLS. (eg MySQL) – Michael Kutz Jun 27 at 12:15
  • Do you give users direct access to SQL statements? Or force them through an API with? Or through Stored Procedures? – Rick James Jun 27 at 16:48
  • API based. A client can share the image with other clients, but an image only has one "owner" – Leon Jun 28 at 17:13
2

A typical implementation of "multi-tenant" is to have a database for each client.

  • Each database has the same few dozen tables.
  • Security (if you care) is somewhat available at the database level.
  • If your product becomes insanely popular, sharding is relatively easy to add (each server would have several clients).

A downside is the thousands of tables in the whole system.

There would be an extra database for admin stuff -- list of users, their passwords, etc.

If you implement with a single set of tables, then

  • Client_id would be in many of the tables.
  • Most indexes would be composite and start with client_id.
  • Many:many tables may or may not avoid including client_id, depending on whether the Entities have their own id.

Prototype now; analyze; but plan on a major redesign of the schema 6 months from now. Without the overhaul, you will forever be swearing at yourself for making the wrong decision today. And your product will die because it could not flex to meet future needs.

| improve this answer | |
  • separate databases? hmmm... i feel that won't work well for thousands of clients. I suppose my case is more about clients owning, sharing and viewing their own content. Later we will tackle the problem of custom data types - user defined "tables". I still feel that separate databases is going to be a mess to maintain, when I am essentially talking about a one-many relationship between a record and client (owner) – Leon Jun 28 at 17:17
  • 1
    @Leon - I generally oppose multiple databases, but there are arguments for it. And some big names (eg, WordPress) do that. Others -- messaging, photo sharing, etc. -- don't, for the reasons you mention. – Rick James Jun 28 at 17:24
  • 1
    @Leon - "User defined tables" -- That is getting more into DBaaS. That leans toward giving each client a VM. You can't give them 'everything'; see if you can focus the target better. – Rick James Jun 28 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.