Hi,

I am trying to design a db schema (or rather extend existing db
schema). My goal is to extend the database table so that every time a
new column is needed, we dont go about adding the new columns by
altering the table. Here is some background.

Business requirement: We usually have a need to extend the existing
table structure to add new columns to incorporate information related
to the new customer requirements.

Example: Consider an existing table "Organization" in our db. I have a
new project from another customer which requires some new information.
To achieve this, I will need to add new columns to existing
"Organization" table. In the future, there is another project from a
new customer which requires adding some more new columns to the
"Organization" table. My goal is not to add new columns every time a
requirement comes to the existing table structure. So I thought about
creating some Metadata table to solve this.

Add a new table called "MetaData". It will have the following columns

column_id (int)
table_id (int) - holds the table object_id for which the new column is
being added
column_name (varchar) - name of the new column needed
column_data_type (varchar) - data type for the new column needed

I will add a new table called "OrganizationAttributes". It will have
the following columns
org_id (int) - foriegn key to Organization table
column_id (int) - foreign key to MetaData table.
column_value (varbinarymax) - the value for the new column for the
org_id record

I will need to create a function called
GetOrganizationAttributes(table_id, column_name, org_id) that would
convert the value in column_value column to appropriate data type.

Is this a good design to implement?. Did any of you use this type of
design in your db schema ? Are there any other better ideas that you
guys think are good.

I appreciate any help regarding this.

Thanks,
-Raj

The design solution that you describe seems very much like EAV design. In general this is considered bad practice - you
cannot enforce data integrity, queries can become very complex and inefficient, etc. I would suggest reading the
following article before going forward:
http://tonyandrews.blogspot.com/2004...-mistakes.html

With that being said, there are cases where EAV may fit - frequently changing attributes where data integrity is not
important and there are no requirements to write complex queries against these attributes (in general you have to pivot
the attributes to query).

One solution that may work and still preserve good design practices is using entity subtypes. That is for different
customer types you can have different tables to describe the unique attributes. You still have to add tables/columns,
but the existing customer types are not affect and there is no redundant data. Here is one example:
http://consultingblogs.emc.com/david...-Subtypes.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

On Feb 12, 2:37*am, Raju_QC <rmanch... (AT) gmail (DOT) com> wrote:
Are you familiar with the predicate view of data? Basically, instead
of entity tables, you make predicate tables - tables which describe
facts about one or more entities. It's an important point of view
from relational theory and object-relational modeling, and has
additional advantages, such as mostly eliminating the need for nulls
and turning queries into logical deduction.

It requires you to create new tables when you need new attributes,
which requires some getting used too. Relational databases can be
much more dynamic and powerful than commonly used.

Could you post some links to articles that go into more detail on this
concept?

Thanks,

Bill

On Feb 23, 3:11*am, bill <billmacle... (AT) gmail (DOT) com> wrote:
AFAIK, it really isn't well described outside of database theory
books. The following links have some info, but I didn't read through
to see if they discuss it in depth.

http://en.wikipedia.org/wiki/Relational_model
http://www.dbdebunk.com/page/page/622423.htm.
http://c2.com/cgi/wiki?DatabaseIsRepresenterOfFacts

The archives of comp.databases.theory should also contain some info.

If you're willing to hit the books, try C. J. Date's "An Introduction
to Database Systems" or "Database in Depth: Relational Theory for
Practitioners". Alternatively, there's "Applied Mathematics for
Database Professionals" by de Haan and Koppelaars.

Related information is available from Object Role Modeling (http://
www.orm.net/), deductive databases, logic programming, etc.