Re: Re-design of data storage table
Date: Fri, 20 Feb 2009 22:30:17 +0000 (UTC)
Message-ID: <Xns9BB8F2AB3EBBBYazorman_at_127.0.0.1>
Paul (paulwragg2323_at_hotmail.com) writes:
> As a new attribute is added it takes up a column in the data table.
> Clearly this is bad design but it is how we inherited the database and
> finally we are looking at a way to change this. As it stands there are
> limits on the number of numerical, textual and date attributes that
> can be added to the system – we provide a certain number of columns
> for storing the data, once these limits are reached no more attributes
> can be added.
>
> We wish to change the way this data is stored so that the limits can
> be removed completely. As I say, I have a solution in mind but I do
> not want to influence how others would approach this. So really the
> challenge is how this can be solved in order to completely removed
> limits on the number of attributes that can be stored within the
> system, whilst ensuring that performance does not suffer. We are
> looking at storing millions of data records.
I cannot comment what would be the best solution in your case, since you you have not given very much details on it. But this is a situation where no solution is really palatable.
A common approach is to make the column rows. This is known as EAV, Entity-Attribute-Value, and there is no shortage of people who points finger at it. Personally, I think EAV is fine if used with care and in only in special cases. EAV gives you lots of flexibility, but writing complex queries is difficult their performance will be horrible.
Another approach is to use XML, but that too makes query-writing difficult.
A very orthodox approach is to make a proper database design from the start, but that system may not be very flexible.
Yet another approach is the one you seem to have now. As you've noticed, it has some limitations, but it makes query writing easier than EAV.
In the end what matters a lot is how complex queries you need to write against this data.
-- Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspxReceived on Fri Feb 20 2009 - 16:30:17 CST