CODEWITHSUNDEEP
mysqldatabasedatabase-design
Richard Deurwaarder
Richard Deurwaarder

Reputation: 2040

How to create dynamic 'types' in database

My question relates to a php website with a mysql database.

Currently in the database we have a 'token' entity

a token can hold 0 or more 'object' entities and each of these objects can be of type x, y, z

so

----------
| Token  |
|--------|

--------------
|token_object|
|            |
|tokenID     |
|objectID    |
--------------

------------
| object   |
|          |
|objectType|
------------

------------  --------- ---------
| objectX  |  |objectY| |objectZ|
|          |  |       | |       |
------------  --------- ---------

Now this works okish, because when I want the data from an object which, due to the nature of our caching system, is almost always one object at the time I can just make two queries to get the data from object and objectX/Y/Z

(SELECT * FROM object WHERE id = $id)

and

switch($objectType)
case 'objectX':
    SELECT * from ObjectX WHERE id = $id
case 'objectY':
etc etc..

But now I've been asked to allow users of the website to create new object types, ie objectA/B etc.

I could just create a new objectXn table for each new object type, and by choosing a good value for objectType that would work

(SELECT * FROM $objectType WHERE blabla)

but that doesn't seem to be a very clean solution, especially the creation of new tables

Are there any better solutions?

Worth noting is that I expect that there are a lot of objects relative to tokens and that the creation of extra object types won't happen a lot/almost never

Upvotes: 1

Views: 675

Answers (1)

Deathspike
Deathspike

Reputation: 8770

What exactly are you storing? Does it need complex queries against it or do you need a simple object-storage by id? In the latter case, you can create a table that has an ID, Type and Data, where data is the serialized object that you can restore in your code (for example, storing your object as JSON, and de-serializing it with the stored Type and the Data). A little more context in your question is appreciated if this doesn't satisfy your needs.

Upvotes: 1

Related Questions