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?
- 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
- 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 ...
- 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+)