On the topic of the "things are too hard for non developers or they are too simple for real developers", I'd probably lean towards the core design being the API for developers, with the expectation that I'll later construct a simpler facade around that API for the non-developer audience.

@ Michael: Well the main problem for me is that I am not interested in solving that kind of problem. I prefer getting into the challenges of non programmers, not really trying to solve the problems of other programmers ..

@Chuck: The problem is that with going at things with being developer friendly at the start is that it will be hard to get the critical mass that these end user centric solutions have. The problem is that end users drive the budgets, they are the ones that install these end user centric stuff, think they got it .. then hit a brick wall and want real developers to fix their existing stuff. Also the end user friendly solutions provide tons of semi working plugins that make for great theoretical rapid development and fancy demos in no time.

In combination with non universal auto generated primary keys it means that not only will it be hard to keep development and production in sync. Some things done inside the admin panel is classic content but a fair bit is configuration which we traditionally want to store inside our software code management repository. Not sure how, but my dream CMS would know the difference and commit configuration to my SCM and it would not depend on meaningless integer ID's that only have meaning inside a single database.

Could you elaborate on that? I do not have experience with a scm-tool, but maybe you could offer a practical example which shows the naked problem

Ok say you have a way to add modules from the admin panel. These get added to the database. The added module gets a sequentially generated ID, you associate that module with a page. All is well. At the same time someone on their development machine writes a new module and also adds it via the admin panel to the setup on his development machine. He also associates the module with a few pages, configures a few things etc. Now you have a few issues, how do you get all the stuff you configured in the admin panel synced to the production database when it becomes time to deploy?

First most CMS (all?) that I have seen that offer a powerful admin panel (which is what draws customers to these CMS to begin with) do not really offer a good way to export the entire configuration of modules and referenced pages. Furthermore even if you go into the database and manually extract the DB changes, you also need to script somehow all the auto generated ID's so that they do not collide with those already generated on the production machine. Heck even sharing the current state with other developers requires this work.

Another scenario which I already mentioned is preparing a set of features that you want to prepare for specific clients. These again consist of some custom module as well a fair bit of configuration usually done via the admin panel. Since the configuration is all in the database with all sorts of generated auto ID's this all becomes a lot more painful than it needs to be.

Also note that since in the end you script direct database changes, you are by passing a lot of potential sanity checks that are usually done as part of the configuration process. For example maybe something works on the development machine because you do not also have this other thing configured which is on the production machine, this would normally be caught by some logic in the admin panel, but since you are side stepping the admin panel the issue is not detected.

At some point you start thinking about crazy stuff like recording your admin panel configuration steps in the development machine with Selenium so you can reply it on the production machine .. this simply cannot be the way.

The solution can of course be that you have some tool that can make a perfect export of a module and relevant associated pages. But I have yet to see a CMS provide this. I think a more natural approach than having to have some magic database export tool is to keep configuration out of the database, so you can package up a new module as a set of files you can upload via the admin panel for example. It just seems like a cleaner "API". Of course you still need some way to sync up content that is prepared in the staging environment as well, so there is still a new for export tools from database content as well. Anyways, I am not claiming that I have the answers, I am just in a lot of pain ..

Ah, that was quite informative Lukas. All clear now. I never have thought about such things.
Well, if I need to build the next CMS I will think about your complaints :D

I have had the exact same problem you have. I use postgresql for production and development and my solution is to have both databases use the same sequence for serials.
This eliminated the problem of not being able to synchronize one way or the other and keeps a nice uniqueness to all that is done in two databases (actually schemes, but that's another discussion ;) )

If I want to clone my scheme and test another setup I still have the uniqueness I need because 1 sequence is used.

I have no idea if this is possible in MySQL or any other rdbms but I haven't had the need to find out. Maybe I will now, just to be informed for the future.

Probably most CMSs suck in the beginning because it is built from a programmer's view and not from a DBA's view. Successful projects abstract the code from the sql and that makes the cooperation between a programmer and a DBA possible.

I am neither a good programmer or a DBA, but ask questions to both people who are to find a solution like the one I mentioned at the start.

Hope this is a solution you can work with.

Just to be clear on this: I use 1 sequence per table, not per scheme ;)