Dynamically creating new database fields and retireving data from them
-
Hi all,
Can someone please help me with this.
There is a need for my application to allow users to create new fields. I could either create a table to hold these fields and put the data into this or I could allow them to modify the actual live tables.
I am thinking that it may be better to go down the route of using a separate table (UserDefined) to hold them with a structure and someone suggested the following: -
UserDefinedID - Sys generated Primary Key
FieldName - the name of the field
FieldType - data type of the field (string, numeric or datetime)
RelatedTable - Indicates which real table this is to be a user defined field for
RelatedTableID - ForeignKey to the Primary Key of the related table
StringVal - If the FieldType is a string, actual data goes here
DecimalVal - If the FieldType is a numeric, actual data goes here
DateVal - If the FieldType is a datetime, actual data goes here
Is this a good approach to use ?
The reason I am thinking of it is, if several different copies of the system are sold, users may make different mods to the structure, and by doing this, the main ‘core’ tables will all be the same easing maintenance.
If my original select statement for an employee table was :-
Select a,b,c from employee
how would I manage to hook into that statement all the columns/rows from the UserDefined table (related to the Employee table) which might hold the following (Note that the sample is based on the above structure) ?
- EmployeeNo String Employee 57 1234 - -
- EmployeeName String Employee 57 Fred - -
- EmployeeAge Numeric Employee 57 - 61 -
- EmployeeNo String Employee 10 5562 - -
- EmployeeName String Employee 10 John - -
- EmployeeAge Numeric Employee 10 - 44 -
- DepartmentID String Dept 57 AB123 - -
- DepartmentID string Dept 10 AB124 - -
Any help much appreciated.
CCB
-
Why do you think you need *user-defined columns*?
What would be the reason for this?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I do see application like that. The select statements are not dynamic. They are static for the particular copy which may differ from another sold copy.
As you mentioned, the core tables remain consistent.
-
I need User-Defined columns because I am producing a generic core, and most customers also want to store a several other fields. Legislation will also dictate over time that several more fields will be required. It will be different for each customer, and trying to make it everything to every customer is not feasible and not what is wanted. the maintenance aspect would also be very difficult.
CCB
-
I need User-Defined columns because I am producing a generic core, and most customers also want to store a several other fields. Legislation will also dictate over time that several more fields will be required. It will be different for each customer, and trying to make it everything to every customer is not feasible and not what is wanted. the maintenance aspect would also be very difficult.
I would rather create a base version that covers all columns that there might be, customize it for each customer (you can make easy additional money with it
).Now when legislation changes, its time for an update.
Basically I would stay away from dynamic creation of tables and columns.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
We use a packaged software application that is designed to be used differently by different companies. Their method of allowing customization for each company is to provide generic fields in each table to be implemented at will by any company.
Each table has 50 Description, 50 Value, and 10 Flag fields.
Description fields are varchar(50) and are named Desc1...Desc50
Value fields are float and are named Value1...Value50
Flag fields are actually int and are named Flag1...Flag10
There is also a table called datadict_field that holds:
1. TableName
2. FieldName
3. DisplayName
4. UserDisplayName
The software looks up into this table when presenting the user the columns\fields to be shown\modified.
If the UserDisplayName field is filled in, then the user sees the value placed there as the heading for that field. Otherwise the default name shown comes from the DisplayName field.
It makes it harder to know at a glance what the meaning of the values stored in Desc1 is whan looking at the raw table, but it is highly flexible.
I know many DBA's will have a hard time with this concept because of the shear number of nullable\optional fields, waste of database space, etc., but the concept is sound when the product is meant to be customized by the end user\company.
Think of the actual physical tables as the logical representation of the tables. The physical has actually been moved to the presentation layer.
-
All that may seems very nice until you get the First problem! from that point on you learn to hate the that sort of thing (sorry, No offence I had to call that some how 
. Data integrity will begin to compromise (no constraints , lack of fk, pk, no domain checks ) and Performance will start to suffer if you let that grow !!My advice: DESIGN, DESIGN, DESIGN ... did I mentioned DESIGN
if you take the shortest path in the begining you may find a surprise at the end
* Noel -
U know the table they want to add fields and type of these fields...
U know when they give you the go ahead to add a specific field or a list...
I am not sure what front-end u're using but it should not matter..
Loop throu the fields to be created (fields u know about)
Send "ALTER TABLE... COLUMN... " directive to SQL for each of these fields..
Where will you be 80 years from now? (So, take it easy!) -
I guess this is more of a question *if* this should be done, not *how* it is done.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I definitely would not be dynamically creating tables and or fields in a database I designed. A simple solution for this is to go with an attribute structure for each of your objects. We have written an order entry application here that uses an attribute based structure and have adapted that design for inventory management and other applications. The idea is that you have a table attribs that would have attrib_id, desc, attrib_type, default_val. Then a table called customer_attribs(customer_id, attrib_id, value). Whenever a user wants to create a new "field" they create an attribute. So one location could have attributes of salesperson, delivery instructions and a second locaiton could have Invoice - Copies, Freight Allowance. No stuctural changes needed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
7 years ago, I interviewed with a company that had just gotten acquired by Oracle... In many of their products.. this was implemented... and succesfully...
It is certainly a matter of preference... but as the product matures...
it can be a good thing...
My 2 cents
Where will you be 80 years from now? (So, take it easy!) -
Being acquired by Oracle is not necessarily a good thing. And successfully is not equal to good.
But who am I to judge on this?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I am not going to argue that this can be done.
My point is that in order to build such a structure you are dangerously sacrifycing DATA INTEGRITY constraints and relying more and more on the application which in the end is not where the data is saved
* Noel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply