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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.