Allowing users to define columns....

Question

Post reply

Allowing users to define columns....

Simon G

Ten Centuries

Points: 1285
More actions
January 12, 2010 at 2:23 am

#208936

Hi all,
What would be the most feasible way to allow users to define certain elements of field data via their website control panel? For instance, I have a table for employee's which has a certain locked down set of fields. On one site, a client may decide they need several fields for email accounts where on another instance of the site, the other client may want 3 or 4 note fields.
How could I give the client (site user) the ability to add these varying fields on top of the set employee table?
I was thinking that I could have a table storing field name, type, limitations and the table the fields are 'attached' to. Then, I could have another 'values' table storing the defined field ID and it's value when a user enters / updates a record on the public side of the site.
These dynamic fields wouldn't be heavily searched...they'd merely give a bit of control to the site owner over what data is stored.
This isn't a 'real life' scenario at the moment but I've been thinking about it and it's practical uses. I don't want to give users access to the actual table structures themselves.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

If there are addtl. fields required I would use a form where clients could request it and let the DBA deal with it.

The solution you have in mind could lead to "columns" named like [mail], , [e_mail] ....

All have the same purpose but different column names as well as maybe even different content.

It would mess up any data model, I'd say...

But if I'd have to (= being forced to) do it I'd probably use the way you described...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Simon G Ten Centuries Points: 1285 More actions · Answer 2

Hi,

Thanks for your input. Appreciated. The name entered would just be a label so it might look something like this:

ID Table Name

----------------

1 employees

ID TableID Name Data Type Length

-----------------------------------------

1 1 email nvarchar 100

So, in the value table, I'd store:

Field ID Value

----------------

1 'bob@test.com'

There would be more field data but, I see what you're saying...this would be messy!

noeld SSC Guru Points: 96590 More actions · Answer 3

Search for open schema design or EAV model ... there are a lot of discussions about this.

-Noel

* Noel

Paul White SSC Guru Points: 150467 More actions · Answer 4

EAV - please Lord no!!! :laugh:

Have a look at 2008's Sparse Columns:

http://blogs.msdn.com/sreekarm/archive/2009/01/08/sparse-columns-in-sql-server-2008.aspx

http://technet.microsoft.com/en-us/library/cc280604.aspx

Efficient, built-in, and play nicely with XML. Awesome.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Simon G Ten Centuries Points: 1285 More actions · Answer 5

Very interesting. Cheers Paul.

You learn something new every day!

😀

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Stay away from the EAV especially if your table is going to be heavily searched.

http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/"> http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/