User Defined Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

User Defined Columns

Author

Topic

notmyrealname

98 Posts

Posted - 2009-04-30 : 07:49:07

Hi.

I'm trying to allow the end user of my app to defined custom table columns that can store additional info that does not come standard with the app. I would like to create the new columns in the main table with all of the other standard columns. The one problem i am running into is that i have stored procedures that get data from this table. The results are grouped by several columns. I also need to select * to make sure i get all of the user defined columns. I can't use select * with group by.

Any suggestions?

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-30 : 07:58:18

Don't add them to your main table. Create an ancillary table specifically for this purpose, using an entity-attribute-value structure:

CREATE TABLE ExtraColumns (ID int NOT NULL REFERENCES MainTable(ID),
AttributeName varchar(50) NOT NULL, 
AttributeValue varchar(100) NOT NULL, 
CONSTRAINT PK_ExtraColumns PRIMARY KEY(ID, AttributeName))

And yes, this is a crappy design, but it's a whole lot better than adding columns to an existing table. The GROUP BY is no big deal, and you can use PIVOT or FOR XML to reformat in a columnar presentation.

This is one of those cases (IMHO, the only one really) where using XML would make (some) more sense. There's really no good way to support this kind of feature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources