EAV Alternative to User Defined Fields?

Question

Given that users must be able to define their own fields on an object, and define allowed values for these fields - on the fly (no compile) - how would you solve this without using EAV?

Example: All objects need one or more reference to the 123 statusnumber (1.a or 2.b or 3.c allowed).

Answer 1

Relational databases really rely on the presence of a pre-defined and stable schema. Maybe you should look at document-oriented databases instead. They generally allow free definition of attributes, per document, and on-the-fly.

As for the schema validation, that probably needs to be done in your application if the schema itself can be changed by the user.

Answer 2

If you must use a relational DB, then there is a (clumsy) workaround. I would also recommed EAV (if you can) or Thilo's suggestion. Here is the relational way of doing it.

Be forewarned. Here are the limitations of this approach:

1. We are assuming a maximum bound on the number of columns a user can create for each data type.
2. We will have sparse tables if many users use only a few of the columns
3. We need the notion of a user who adds meaning to each column
4. It is a gross violation of Normal forms

_

create table main_tbl(

numColumn1 number(10),

numColumn2 number(10),

numColumn3 number(10),

numColumn4 number(10),

numColumn5 number(10),

charColumn1 varchar2(100),

charColumn2 varchar2(100),

charColumn3 varchar2(100),

charColumn4 varchar2(100),

charColumn5 varchar2(100),

dateColumn1 date,

dateColumn2 date,

dateColumn3 date,

dateColumn4 date,

dateColumn5 date

)


create table main_tblmeaning(

user_id varchar(25) foreign key references users_tbl (user_id),

numColumn1_name varchar2(50),

numColumn2_name varchar2(50),

numColumn3_name varchar2(50),

numColumn4_name varchar2(50),

numColumn5_name varchar2(50),

numColumn6_name varchar2(50),

charColumn1_name varchar2(50),

charColumn2_name varchar2(50),

charColumn3_name varchar2(50),

charColumn4_name varchar2(50),

charColumn5_name varchar2(50),

dateColumn1_name varchar2(50),

dateColumn2_name varchar2(50),

dateColumn3_name varchar2(50),

dateColumn4_name varchar2(50),

dateColumn5_name varchar2(50)


)

create table users_tbl(

user_id varchar2(25) primary key,

user_name varchar2(50)

)

Each time a user wants a number column you assign him a free number column (numColumn1 - 5) in the main_tbl . Add an entry (row) in the main_tbl_meanings that would map the column (numcolumn1-5) to a user readable name supplied by the user.