How to design a database for User Defined Fields?
If performance is the primary concern, I would go with #6... a table per UDF (really, this is a variant of #2). This answer is specifically tailored to this situation and the description of the data distribution and access patterns described.
Pros:
Because you indicate that some UDFs
have values for a small portion of
the overall data set, a separate
table would give you the best
performance because that table will
be only as large as it needs to be
to support the UDF. The same holds true for the related indices.You also get a speed boost by limiting the amount of data that has to be processed for aggregations or other transformations. Splitting the data out into multiple tables lets you perform some of the aggregating and other statistical analysis on the UDF data, then join that result to the master table via foreign key to get the non-aggregated attributes.
You can use table/column names that
reflect what the data actually is.You have complete control to use data types,
check constraints, default values, etc.
to define the data domains. Don't underestimate the performance hit resulting from on-the-fly data type conversion. Such
constraints also help RDBMS query
optimizers develop more effective
plans.Should you ever need to use foreign
keys, built-in declarative
referential
integrity is rarely out-performed by
trigger-based or application level
constraint enforcement.
Cons:
This could create a lot of tables.
Enforcing schema separation and/or a
naming convention would alleviate
this.There is more application code
needed to operate the UDF definition
and management. I expect this is
still less code needed than for the
original options 1, 3, & 4.
Other Considerations:
If there is anything about the
nature of the data that would make
sense for the UDFs to be grouped,
that should be encouraged. That way,
those data elements can be combined
into a single table. For example,
let's say you have UDFs for color,
size, and cost. The tendency in the
data is that most instances of this
data looks like'red', 'large', 45.03
rather than
NULL, 'medium', NULL
In such a case, you won't incur a
noticeable speed penalty by
combining the 3 columns in 1 table
because few values would be NULL and
you avoid making 2 more tables,
which is 2 fewer joins needed when
you need to access all 3 columns.If you hit a performance wall from a
UDF that is heavily populated and
frequently used, then that should be
considered for inclusion in the
master table.Logical table design can take you to
a certain point, but when the record
counts get truly massive, you also
should start looking at what table
partitioning options are provided by your RDBMS of choice.
Design patterns for user defined fields with dropdownlist support
Are there any suggestions, and considerations I should make?
Yes. Start over, and let the DBMS do the work! That DataType
column is a warning bell that something is wrong. The DBMS provides types, type safety, and type conversion.
Separate your UDFs into CustomIntFields
, CustomStrFields
, and CustomDateFields
. If desired laster, you can represent them as a single view, using a UNION
:
create view CustomFields as
select 's' as type, FieldID, Name from CustomStrFields UNION
select 'i' as type, FieldID, Name from CustomIntFields UNION
select 'd' as type, FieldID, Name from CustomDateFields;
Just for starters, that will let the DBMS ensure on your behalf that dates have dates and integers have numbers.
The DropDowns
table becomes
create table DropDowns
( DropDownID int -- indicating the widget
, type char(1)
, FieldID int
);
referencing the union of the the three UDF tables.
This design lets fields be added without automatically appearing in the dropdown, which might not be what you want. If every field is supposed to appear in only one particular dropdown, the dropdown ID could be added to the three field tables and everything driven from the view.
What would be the most efficient way
This stuff is all very static and small. I have a hard time believing efficiency will be an issue. But I do think programmer and customer satisfaction will be higher by using the DBMS in the way it was intended. :-)
Implementing and indexing User Defined Fields in an SQL DB
It seems like you've listed your available options. EAV can be a pain for querying (and slow, depending on how many criteria you want to search on simultaneously), but it tends to be the most "sane" and RDBMS-agnostic implementation.
Modifying the schema is a no-no...obviously it can be done, but such a practice is abhorrent. I do not approve.
The XML option is a solution, and SQL Server can query inside the structure. I'm not certain about other RDBMS's, and you don't list which one you're using in the post or the tags.
If you're going to be querying on many attributes (say, 20+) simultaneously, then I would probably recommend the XML solution just to limit the number of joins you'll have to make. Aside from that, I would stick with EAV.
How would you create and store user-defined custom fields in a SQL database?
We add almost in our all application/products additional attribute/field support for given flexibility to user
Like we have a product category, In the category, customer can define additional attribute of any product
what we are doing in the DB level is:
Category Table have some additional column like: Text1Att, Text2Att...for text value support, Num1Att, Num2Att... for Number value support, Date1Att, Date2Att... for datetime value support, ID1Att, ID2Att... support for ID from other table like you can add dropdown, listbox,...
here all the column have String datatype.
what we store here is
we will store meta information here, like for Text1Att meta is
SSN;textbox;50;true;false;Null;
Caption of field;Control Type;Max length;is Required field;is Custom validation required; Custom Validation message;
birth place;textbox;100;true;true;Invalid Value;
Same for Numeric field ...
for date meta information will look like
birth date;Calendar control;true;true;Invalid Date;
Caption of field; Calendar control or can be other;is required;is Custom Validation; Custom Validation message;
What are doing in product table is add same number of column and have datatype text1Att,.. is varchar, num1Att have numeric, date1Att have datetime, ID1Att have int
What we are doing GUI side is : In category definition page add these attribute and build meta information at runtime and store in category table
On the other hand when we define product in category, meta information will be read and traverse from category table and populate in product definition page like other fields.
if u need further help, I can provide you images so that you will better understand how can be done this.
we are experience and analyze, this is much flexible approach
Related Topics
Postgres Unique Constraint VS Index
Mysql, Iterate Through Column Names
How to Handle an in Sub-Query with Linq to SQL
Calculate Business Days in Oracle SQL(No Functions or Procedure)
Group by Without Aggregate Function
Does MySQL Have an Equivalent to @@Rowcount Like in Mssql
SQL Statement to Get Column Type
Get Next Sequence Value from Database Using Hibernate
Pls-00428: an into Clause Is Expected in This Select Statement
Oracle Query to Fetch Column Names
How to Select SQL Server Data Using Column Ordinal Position
Could Not Find Stored Procedure 'Dbo.Aspnet_Checkschemaversion'
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
SQL Server Ignore Case in a Where Expression
How to Run a Stored Procedure in SQL Server Every Hour