Avoiding Inner Platform

[jhmartin]

Can anyone offer suggestions on how to deal with applications that change their columns at will? Jira, for example, allows the admin to define a set of custom fields for each kind of ticket.

Jira implements this by defining a table for all custom fields, with a field defining which field this is. Does anyone have any suggestions on how to do something similiar without forgoing RI like this method does?

[PAVB]

You decide either to use it or not to use it.
If you decide to adopt it then, deal with it; the vendor is not gonna change it because you don't like it.

[jhmartin]

Well yes, obviously in this case I can't change Jira. I am using Jira as an example of one way it has been done, and I am asking how one might do it a different way.

So really, I'm just asking "How could I accommodate a mutable schema without implementing it the way Jira did?"

[PAVB]

1- Get a DBA with experience in Data Modeling and, Database design
2- Put the guy in charge
3- Let the guy earn his/her salary and, relax

[gamyers]

I'm just asking "How could I accommodate a mutable schema without implementing it the way Jira did?"

Are we talking about an application you are writing ?

One option is go Open Source, so if the customer wants new columns, they adapt the application.
Another option is, if the customer wants new columns, they pay you money to add them and customize the application accordingly.
Third option is, work out all the possible columns and build them into the application in the first place.

[jhmartin]

I'm not writing such a thing at this time, but just as a thought experiment I was wondering how one might provide the flexibility offered by the Inner Platform method that Jira uses, yet still follow reasonable DB design. I'm bound to either need to implement something like that in the future, or at least talk to someone who does. I recently had to reverse engineer a Jira database to locate a certain data field, so my distaste for the Inner Platform method is fresh in my mind.

Jira, for example, does not require any DBA or dev intervention to add new fields. Because of this, it doesn't make sense to say "don't do inner platform, instead spend either $x or X hours to add a field". A Jira admin just goes into the GUI and defines a string field, for example, and that field gets a type id and attribute ID, which points it to the String table with a given attribute id. An entry is also added to the field list for issues of type X to tell Jira to look that value up. Total effort: 5 minutes.

The best I can come up with is that one would create a set of stored procedures which could allow the (theoretical) admin GUI to add/delete specially named columns to a table, and also create an index on that column. If field is based on a multi-select, a code table would have to be generated and the appropriate FK set. Thats the closest thing I can think of to emulating the functionality / flexibility to the way Jira does it with the Inner Platform.

I agree that for most systems it is better to have the schema defined in advance with changes made in a rigorous process. However, applications such as Jira demonstrate that users expect greater flexibility in some situations.