Adding fields to a table - 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

General SQL Server Forums

Database Design and Application Architecture

Adding fields to a table

Author

Topic

Quentin
Starting Member

12 Posts

Posted - 2013-02-14 : 19:09:32

I am looking into the options for users to create additional fields.

If I have a base table of STAFF with fields (StaffID, Surname, Firstname, Address etc) as a main table.

I then want the users to be able to add fields as required (birthday, favouritecolor, nickname) these fields can basically be anything in addition to the standard data.

I then want an overall view of all the base table fields PLUS the additional fields.

I was thinking along the line of a MAIN table with key of STAFFID,
A CODE table with the fields NAME, DESCRIPTION to use as the depository for the additional fields created by the user
A DETAILS table which contains the STAFFID (from MAIN table) , NAME (from CODE table) and VALUE being the data stored.

example
MAIN - STAFFID - 123456, 234567, 345678
CODE - NICkNAME, A persons Nickname
FAVCOLOR, Favourite color
DETAILS - 123456, NICKNAME, BOB-SLED
234567, NICKNAME, TINYTIM
345678, FAVCOLOR, BLUE

Questions
1. Is this the best way to represent the data
2. How do you create a view for
STAFFID NICKNAME FAVCOLOR
123456 BOB-SLED NULL
234567 TINYTIM NULL
345678 NULL BLUE

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-15 : 06:47:53

What you are proposing is certainly possible, but as you are seeing, it takes some effort to present the data the way you want. It can be done, but it is not painless. This method - the so-called EAV method - has its own baggage.

Another possibility that I want to suggest is to create this auxiliary table with a fixed number of "UDF"s (user-defined fields)' There can be a few integer columns, a few varchar columns etc. in this table. The disadvantage is that the user will be limited to a certain number of custom fields. But querying and maintaining is easier. Would that be a possibility for you?

Quentin
Starting Member

12 Posts

Posted - 2013-02-18 : 23:59:18

Thank you for the feedback.
Although your suggestion is somewhat restrictive, it does present another option. I will consider it and discuss with those involved to see if a limitation of that nature is possible.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources