I created a table in a Postgres database. I would like to create a form in a Java application to insert rows into that table. Certain fields have default values. What is the best way for the database and UI to agree on what the default values are? Also, what is the best way for the form to communicate the user's intent regarding defaults to the database?

  • 1
    Why does the database need default values if it's expected that the UI is still providing them. – Matthew Oct 10 '14 at 17:38
  • It is nice to store defaults in the database so that they apply to every user/application that uses the database. – Reinstate Monica Oct 10 '14 at 17:50
  • I'm no DB expert but anything you would just read once from a DB on app initialization smells funny to me. – Erik Reppen Oct 10 '14 at 18:04
  • Erik, who is suggesting that? – Reinstate Monica Oct 10 '14 at 18:25
  • Do we need to know if someone is set to default vs set to 5 and 5 happens to be the default value? – stonemetal Oct 10 '14 at 19:33

Referential integrity and other constraints should be managed by the database and not the application that uses it.

In the case of default constraints, I would have the UI pass in NULL for those fields or simply do not specify them at all in the INSERT query. The database will then supply the default value.

The benefit to this is that SQL is designed for the task of managing data, so manage the data (including defaults) in the tier that handles it best. If you have multiple application using it, data will be consistent. If someone opens a query window to manipulate data, data will be consistent. If someone uses a bulk insert or other data load method, data will be consistent.

The key here is everything funnels into the database: it is a data chokepoint. That makes it the ideal place to manage constraints consistently.

| improve this answer | |
  • I agree philosophically but it would be nice for the UI to display the default values initially - how is that possible if the defaults only live in the database? I suppose the UI could display blank values and allow the user to leave the fields blank if the user doesn't want to set values but that isn't as friendly. – Reinstate Monica Oct 10 '14 at 19:43
  • It is possible to extract the default values from the database, but the specific method is unique to each database. Typically it would involve querying system views. – user22815 Oct 10 '14 at 20:38
  • 1
    @jef: insert a null record and read it back to find the default values set by the db – x-code Oct 10 '14 at 20:41
  • @x-code, that's clever, albeit a hack. It seems to be the best option suggested thus far. – Reinstate Monica Oct 10 '14 at 20:44

Preferably, you should construct your workflow so that new records are initialized in the GUI only and not from the database. That will ensure that front-end and back-end don't need to agree about the default values, because the DB defaults never become visible to the user.

If you must process adding just like editing, i.e. the front-end is loaded with default values read from the server, then you should look into auto-generating one of the two sides - either auto-generate the SQL creation script from the front-end code or vice versa, or auto-generate both from a separate data description document.

| improve this answer | |
  • There may be multiple applications using the same database. It would be ideal to keep the defaults in one place. Auto-generation causes duplication - you need to remember to regenerate when things change, which is just like updating the values manually except possibly less error prone; either way it's suboptimal. – Reinstate Monica Oct 10 '14 at 19:41
  • Sure. But so is using a compiler vs. hand-assembling: exactly the same except possibly less error-prone. The entire point of auto-generating things (in fact, the entire point of using a computer) is to make things more convenient and less error-prone. Auto-generated files reduce duplication because you have to maintain stuff only once and the build system takes care of the rest. They don't cause duplication any more than writing a subroutine and then calling it multiple times. – Kilian Foth Oct 10 '14 at 19:51
  • It seems like overkill to use autogeneration and significantly complicate the build process just to synchronize default values. Perhaps it would be worth it in a large system but in the tiny application I'm developing, I don't think it's worth doing. – Reinstate Monica Oct 10 '14 at 20:28

Doing both should be considered an option. There are instances where the logic is too complex and relies on data from so many other tables, that the database code is just a bad place to handle it. If the goal is to consistently put them in the same place(s), you may not want to violate this, so keep it all on the form.

On the form, you can prepopulate default values, so the user knows what they are and can potentially change them or if it is some value unimportant to the user and/or you don't want them to change it, you have the flexibility to leave it off the form and let the db handle it.

This way, if you do any type of date import/transfer, you know the rules will still be upheld.

Of course the downside is the duplication of effort and keeping things coordinated and supported down the road. There are two potential places to determine, "Where did that value come from?"

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.