Prev: Request for help with odbc driver for Python / SQL Server 2008
Next: ODBC Connection Wizard: Can't see SQL Server
From: Raju_QC on 11 Feb 2010 19:37
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
From: Nilone on 21 Feb 2010 04:56
On Feb 12, 2:37 am, Raju_QC <rmanch...(a)gmail.com> wrote:
> 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

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.
From: bill on 22 Feb 2010 20:11
Could you post some links to articles that go into more detail on this
concept?

Thanks,

Bill

> 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.- Hide quoted text -
>
> - Show quoted text -

From: Nilone on 23 Feb 2010 03:06
On Feb 23, 3:11 am, bill <billmacle...(a)gmail.com> wrote:
> Could you post some links to articles that go into more detail on this
> concept?
>
> Thanks,
>
> Bill
>
>
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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.
 | 
Pages: 1
Prev: Request for help with odbc driver for Python / SQL Server 2008
Next: ODBC Connection Wizard: Can't see SQL Server