Using custom prefixes for custom tables — discussion

Development
1

I’ve been involved in a number of projects that use custom tables, some of which are quite extensive. For the extensive ones, I’ve found that using a custom table prefix works well. I create and update tables in my regular MySQL application, then use Bob Ray’s “CreateXpdoClasses” snippet (found here: Bob's Guides | Custom DB Tables) on the site to create the component schema and class files. Then, whenever I update the tables, I just rerun this script and all the schema and class files get updated accordingly. It’s fast and easy.

There are some who say the best idea is to just use the default MODX prefix (modx_, or whatever you use), and I see why that’s desirable in many cases, especially if you’re developing an extra for distribution. But for custom work that won’t go public, I’m curious to see which approach people have used.

Mark Hamstra posted this a few years ago:

The modx_ prefix is only a default, and the idea of prefixes is that you can have multiple MODX installations (or even different CMSes) in a single database table. By using a custom prefix, you remove that flexibility.

That makes sense, but I can also argue the opposite: using a custom prefix allows you to share data across multiple MODX installations.

On the other hand, Bob Ray says this on his Custom DB Tables page (referenced above):

It’s a good practice to use a different table prefix than the one in your MODX database . . .

MIGX DB has an option to set up custom tables with custom prefixes, and Bruno replied to one person’s query with this:

for working with custom-tables inside modx, you can do it this way:

 1. give all your tables for your package a unique prefix, lets say ‘mybookings_’
 . . .
 7. set the listbox to ‘use custom prefix’ and put into custom-prefix: ‘mybookings_’

Has anyone used custom table prefixes when creating a system that uses custom data?
If so, why?
If not, why not?
What pros and cons have you found using either approach?
Have you changed your opinion over time?

2

If your use case is indeed to share data across, then a custom table prefix (xPDO::TABLE_PREFIX) is indeed perfect, to illustrate it’s separate and access it the same way in all sites in the same database.

In all other use cases, my experience is that a custom table prefix (xPDO::TABLE_PREFIX) only makes things unnecessarily harder, like having to pass the prefix everywhere you initialise the model.

And if you’re wondering why I keep referring to xPDO::TABLE_PREFIX: it’s still a good idea to add some kind of prefix to make sure your table names are unique, for example <object name="SomeCategory" table="mycomponent_category">: that mycomponent_ prefix there is always a good idea to avoid running into conflicts. Just don’t specify it as xPDO::TABLE_PREFIX unless it is meant to be used outside of MODX.

3

If I would need to share some tables across multiple MODX instances, I would probably use a seperate database and probably I would build an API to access them.

If the tables are used only in one instance at the same time, I would use the default prefix, but allways use a second prefix behind the MODX prefix, for example modx_mypackage_mytable.
More than once, I had to work with tables with custom prefixes and had issues, because I forgot to pass it or a snippet didn’t support it or something like that.

1 Like
4

In all other use cases, my experience is that a custom table prefix (xPDO::TABLE_PREFIX) only makes things unnecessarily harder, like having to pass the prefix everywhere you initialise the model.

I initialize the model globally using a plugin so that I don’t have to do that in more than one place! I understand that some extras may have trouble with custom prefixes, though. But, the way I work, I primarily use PHP coding, not snippet tags embedded in HTML, so I don’t run into that problem myself. For the sites in which I use this approach, there aren’t any other extras that access this data anyway, so there is no possibility of an incompatibility.

5

That seems like more work than just using a custom prefix and then being able to instantly use the regular MODX API, unless I’m misunderstanding what you mean.

6

Yes, I always do that, even with a custom prefix, to ensure unique object and class names.

7

One reason I like using a custom prefix is that it’s so fast to update tables without having to do any workarounds. I update the tables using my MySQL tool, then just run Bob Ray’s script to update all the XPO classes, schema, and maps. That’s it. If I don’t use a custom prefix, then I have to something like rename my custom tables with a custom prefix, run the script (either Bob Ray’s, or using MIGX DB) to recreate the XPDO files, then change the table names back to the default MODX table prefix. If there is just a table or two, that’s not much of a problem, but if I have a lot of tables, that’s a bit of a hassle.

8

Usually, when I have to add fields to a table, I do it only in the schema.
With MIGXdb, I have only to select the package and click ‘Add fields’ and all fields are added to the tables and maps for that package are recreated with one click.

9

What I really want is for a way to specify which tables to include in a package. Bob Ray’s script has this capability built into it, here:

$generator->writeSchema($file, $myPackage, '',$myPrefix,true,$myTables);

And then I can put the snippet on a page and run it, like this:

[[!CreateXpdoClasses? &myPackage='surveytest' &myTables='survey_question,survey_respondent']]

But the MODX xPDOGenerator.writeSchema doesn’t have this; it only uses the first four values (up to and including the prefix), so Bob’s script is just kind of waiting for writeSchema to catch up and add this capability. If this were set up, then I wouldn’t need to use any workaround; I would just list my tables in the snippet call, and then I could use the default MODX prefix and then just run the script to update everything.

10

Yes, but I’m trying to avoid having to hand-write schema. I work extensively in my MySQL software when creating and updating tables.

11

Interesting, I’ve always considered writeSchema a single-use utility to create a schema for easily “importing” tables into xPDO the first time. After that, the schema would be the source of truth and you’d be using the xPDOManager to update the tables from that instead. The custom table prefix isn’t a concern that way, because it only affects what’s in your custom schema/model.

Not saying your approach is wrong – just interesting to see others do things I thought were pretty standard completely different. :slight_smile:

12

In addition, it is almost impossible to infer intentions from the database when creating the schema. A single datatype could represent any number of PHP types, indices, etc. that would have to be manipulated each time you ran the writeSchema process on the same tables.