SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Adding fields to a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Quentin
Starting Member

Australia
12 Posts

Posted - 02/14/2013 :  19:09:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3697 Posts

Posted - 02/15/2013 :  06:47:53  Show Profile  Reply with Quote
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?
Go to Top of Page

Quentin
Starting Member

Australia
12 Posts

Posted - 02/18/2013 :  23:59:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000