Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

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).

share|improve this question
up vote 6 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                       <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\n                </div>\n            </div>\n\n            <div id=\"copyright\">\n                site design / logo &#169; 2016 Stack Exchange Inc; user contributions licensed under <a href=\"https://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\n            </div>\n            <div id=\"svnrev\">\n                rev 2016.2.5.3240\n            </div>\n            \n        </div>\n    </div>\n    <noscript>\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\n    </noscript>\n<scri"], "copyright__copyright__copyright": ["mary key,\n\nuser_name varchar2(50)\n\n)\n\nEach 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.\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}