user defined fields best design

[shivprasad koirala]

hi all(happy raksha bandhan day)

we have one of Automation software for sales running for a
customer.He was cool for the first month of product, but later popped
with adding some extra fields.no problems i added in database , put a
seperate code in my application for that field.but later every 2 days
he was adding new fields.....so i thought to put in some inbuilt logic
user defined fields.second his user defined fields are like shoudl be
numeric,string , length validation.But do not know whats the best way
to acheive this.I mean should i make seperate table where i define
field name, data types , validation and then in my application code a
general logic for it in my application code.Any one has prooven design
for user defined fields,just thinking if i can even get a idea.....
When i die i die programming........

[Erland Sommarskog]

shivprasad koirala (sh**********@yahoo.com) writes:

we have one of Automation software for sales running for a
customer.He was cool for the first month of product, but later popped
with adding some extra fields.no problems i added in database , put a
seperate code in my application for that field.but later every 2 days
he was adding new fields.....so i thought to put in some inbuilt logic
user defined fields.second his user defined fields are like shoudl be
numeric,string , length validation.But do not know whats the best way
to acheive this.I mean should i make seperate table where i define
field name, data types , validation and then in my application code a
general logic for it in my application code.Any one has prooven design
for user defined fields,just thinking if i can even get a idea.....

I think the most important here is to get the requirements right. Do the
customer require to be able to add fields himself? And is he prepared to
pay for it?

One thing you could do is:

CREATE TABLE customfields (
fieldcode varchar(10) NOT NULL,
fielddescr varchar(80) NOT NULL,
typeofdata char(1) NOT NULL
CONSTRAINT ckc_cf_typeofdata CHECK -- Int, Date, Float, Varchar
(typeofdata IN ('I', 'D', 'F', 'V'))
minvalue sql_variant NULL,
maxvalue sql_variant NULL,
isnullable bit NOT NULL,
CONSTRIANT pk_customfields (fieldcode)
)

CREATE TABLE customfieldvaleus (
entityid int NOT NULL,
fieldcode varchar(10) NOT NULL,
value sql_variant NULL,
CONSTRAINT pk_customefieldvalues (entityid, fieldcode),
CONSTRAINT fk_entitityid FOREIGN KEY (entityid)
REFERENCES mainentitytable(id),
CONSTRINAT fk_fieldcode FOREIGN KEY (fieldcode)
REFERENCES customefields (fieldcode)
)

You could add more columns to customfields for validation rules.

Generally, tables like this example are more difficult to program
against. For instance, if you misspell a fieldcode in the SQL code,
the compiler won't tell you. I'd say that this structure is only
warranted when there truly is a requirement for custom-added fields.
There is one more case: when fields are added frequently, and most
accesses only are to a single field anyway. We have a couple of
tables of the latter kind in our database, and they are typically
parameter tables.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[shivprasad koirala]

thanks Erland Sommarskog for that 10000000000 worth advice

i has this design in my mind but just thought that was there any mroe
easy way.

take care

when i die i die programming
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!