Problem solve Get help with specific problems with your technologies, process and projects.

Developing a flexible data model to be customized by the customer

I am developing a data model for a product. One of the features that we want to provide is flexibility. What is the best way of developing a data model so that we can standardize it for a product, yet let the customer customize it if needed? For example they will be able to store some data that are specific only to them. It's something that we did not know about beforehand, and we don't have to go and change the model.

Also are there any good books or reference materials that specialize on a datamodel for a product?

Thanks in advance for your help,

There are many ways to approach this problem.

The simplest approach is to simply say, "Don't customize the schema", but that is a tad bit too restrictive.

Another good approach is to build the base schema, meaning the columns, tables, and relationships that your application needs, then tack on a few tables to allow the user to add additional data as needed. For instance, let's say you have a base schema something like:

CREATE TABLE t_products (
   product_id           INT              IDENTITY
,  name                 NVARCHAR(30)     NOT NULL
--  other columns as needed

This would contain all the columns that your application needs, whatever those columns might be.

To allow the user a simple way to add data to your schema, you can add one or two more tables, something like:

CREATE TABLE t_extra_data_types (
   extra_data_type_id   INT               IDENTITY
   CONSTRAINT XPKt_extra_data_types PRIMARY KEY (extra_data_type_id)
,  name                 NVARCHAR(50)      NOT NULL

CREATE TABLE t_extra_data (
   extra_data_id        INT               IDENTITY
,  product_id           INT               NOT NULL
   CONSTRAINT XFK01t_extra_data
      FOREIGN KEY (product_id) REFERENCES t_products (product_id)
,  extra_data_type_id   INT               NOT NULL

      FOREIGN KEY (extra_data_type_id) REFERENCES t_extra_data_types (extra_data_type_id)
,  value                NVARCHAR(4000)    NOT NULL

You could optionally add additional constraints based on the t_extra_data.extra_data_type_id that would enforce the ability to convert a "value" column to a specific SQL data type.

Note that this does not require any or all of the possible t_extra_data_type columns to exist for a given row. Your application would need to enforce that.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.