In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be values that are strings, integers, timestamps, IP-Adresses, etc. It is also possible that I need to add another data type later but this should happen not very frequently. New parameters will be inserted frequently. Values of existing parameters will probably be updated several different times. Parameters and values will be read very frequently but only for displaying them to the user, so without any special analytical operation.

For the later code (which will be C++) I was thinking of a parameter base class from which subclasses for each data type will be derived. StringParameter, TimestampParameter, etc.

I was now wondering how to store parameters in an Oracle 10g database. Ideas so far are

  • Single table, string column: A single database table for all parameters where the column type for the value is VARCHAR2. The code can then transform the values to the correct data type by using functions like atoi. Drawback is I would need an extra type column and transforming values back and forth with atoi and itoa is not very safe and neither good maintainable (e.g. if the data type of one parameter changes from int to long).

  • Single table, raw column: A single database table for all parameters where the column type for the value is RAW. The code then just stores and retrieves the property "as-is" in binary. Drawback is I would need an extra type column and changing the data type of a parameter would break backward compatibility (or one would need to perform a database upgrade which converts all old values to the new ones.

  • Multiple tables, one for each data type: A basic parameter table for all common properties and several extra tables only for the value so that the column can be of the correct data type. Drawback: Not all data types from my domain can be mapped to an oracle data type. Additionally I would need a lot of new tables and joins. Also introducing new parameters would need a change in the database schema.

For which option would you go and why? Is there another solution I have not yet thought of?

migrated from stackoverflow.com Sep 18 '13 at 22:37

This question came from our site for professional and enthusiast programmers.

  • Answer depends on planned requirements for object processing: use database only for storage, perform some modification of objects in database, perform some analytic operations on stored objects etc. So, please update your question with description of processing goals. – ThinkJet Aug 28 '13 at 12:34
  • Added a paragraph on the usage scenario. Hope that clarifies the scope of my question. – sigy Aug 30 '13 at 9:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.