Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EAV Alternative to User Defined Fields?

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

like image 282
comichael Avatar asked Mar 01 '23 08:03

comichael


2 Answers

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.

like image 58
Thilo Avatar answered Mar 08 '23 07:03

Thilo


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.

like image 37
bkm Avatar answered Mar 08 '23 06:03

bkm