General discussion


Modeling columns as rows

By MaryWeilage Editor ·
This week's SQL Server newsletter describes how and when to model a table's columns as rows.

Does this solution apply to development work you're currently doing, or do you think you'll refer to it on future projects? What topics would you like to see the author cover in future editions of this newsletter? Let us know.

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -


by Jeff_D_Programmer In reply to Modeling columns as rows

This can get a little complicated when there are many tables involved, but anything that runs faster than SELECT DISTINCT, I'm all for!

Collapse -


by kurt s In reply to Modeling columns as rows

A generic db will never perform as well as a specific use DB, purely due to
the number of joins required and the complexity that the application must

Programmers like this type of solution because it mimics an OO model they
are familiar with. In terms of relational database design though, it's lousy:
It's extremely difficult to build in any kind of data integrity; Performance
is poor due the excessive number of joins/subqueries needed to make sense of
the data; The Value column is effectively untyped and so requires special
handling in every expression; Indexes are largely ineffective for many

To claim that this design is a "logical consequence of normalization"
suggests a misunderstanding of the concepts and goals of normalization and
relational design. It's just a method of implementing another "abstract"
database model on top of an RDBMS.

Collapse -

What's the best approach then?

by zykem In reply to No-gooder

I have been battling with this issue for the last few weeks. We are building an ASP where we hosts clients web sites. All this is build using one common source code tree and one database structure. However, every client wants a different set of properties on their Register User screen. We would like to make these properties customizable per every clients needs. But what is the best way of storing this information? a) store the properties as rows as in this example or b) store it column-wise.

The problem I see with (a) is that the number of rows can grow very quickly, i.e avg 100000 users per client, avg of 10 custom fields, some fields might have multiple values, and for 5 clients it quickly grows to 5 million rows vs (b) for 5 clients you'll have 500000 rows with 10 columns.

But with (b), isn't it more difficult to get the information out and to store the configuration of every column?

Collapse -

Best Approach

by artful In reply to What's the best approach ...

Battle on, good soldier!

There are a few ambiguities in your response, and without clarification I cannot be certain of what you meant. But I've never been shy to offer opinions in the absence of solid evidence :)

"One database structure" : your subsequent comments imply "one actual database" rather than one common structure with one database per client. If I read that correctly, then I suggest that you immediately abandon that notion and have one database per client... and given your numbers, think in terms of load-balancing, etc.

You identified the "Register User" form as the problem. We can be fairly certain that all clients want certain columns (name, address, city, etc.), so my suggestion would be to keep all of these in a single table called Users. You are then free to deal with the User-Defined-Columns (UDCs), or more accurately to add a table ClientUDCs in which the UDCs appear as rows (as in the tip) rather than columns. The SELECT presented in the tip shows how to grab 3 such UDCs, and further suggests that the SELECT can be generated by the front-end application (the for/next loop presented in pseudo-code).
To embellish this for your situation, join the Users table to the ClientUDCs table on UserID, the latter on the number of UDCs of interest. That will give you all the constant columns and all the virtual columns that are modeled as rows.

I'm happy that my tip was relevant to your immediate problem, and hope that this reply gets you closer to your solution.

Arthur Fuller

Collapse -

Generic vs. Explicit DB

by artful In reply to No-gooder

1. I never claimed that this approach was better for all seasons. I very specifically demarcated the solution to those situations in which supplying what could be a large number of client-specific columns was a core requirement. In that case, you do one of:
a) add lots of columns to every client's tables and ignore those the specific client is not interested in;
b) add some number of generic columns (a la Act!), allowing each client to fill in the prompt, data type and default value for each of X columns. The problem here emerges when the next client wants X+1 UDCs (user-defined columns); or
c) model the UDCs as rows in some table. It was not my intent to suggest that you should model ALL columns in the given table as rows -- only the UDCs.

2. I never claimed that it would be faster. In fact, I explicitly pointed out that it would be slower.

But thank you for helping me point out these limits and provisos.

Arthur Fuller

Related Discussions

Related Forums