I want to write an application that allows users to define their own data base schemas. The user provides a definition using a DSL (biological experiments) and based on this, an oop/entity relation model is generated. The backend should be an RDBMS such as Postgres.
What is the best way to do this?
I can see two possible ways off the top of my head:
What do people generally do here? For 1., permission managent etc. could be handed to the rdbms. However how would one be able to track changes here and allow rollbacks?
Can anyone point me towards best practice?
Best practice depends much on your application's specific needs. For example, Option 1 is appropriate if you are writing an application generator with technically savvy and trustworthy users. This, for example, is the approach taken by PeopleSoft Application Designer. Tracking changes and allowing rollbacks is tricky, since you can't roll back something like a dropped column. You could write some complicated arrangement with a custom audit table to track what was done, and logic to make it reversible. But you would still lose data with that dropped column. If storage is no concern, you could make a backup copy of the table with each change, and the rollback would simply restore that backup. But then you would risk losing data that had been added to the table between the change and the rollback.
On the other hand, if what you are creating is more like a survey generator, then I have done something a little like option 2. Keep a metadata table that shows what a "record" is. The record itself is actually stored in two tables - parent and child. The parent table identifies the record, but all the fields are stored in the child table, as key-value pairs. You have complete flexibility to store whatever you want, and you never have to muck with dynamically changing the database schema. You can even preserve historical versions of the metadata, so that you can always reference historical records without losing data.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments