entity framework - What strategy to use: store additional informational field in main table or in separate with on-to-one/many relation? -

i have main entity , customer want new fields in entity.

as them used informational , never criteria in queries think storing them in main table may worse practice (especially updating schema each update).

what if organise additional table:

create table entity_field (   entity_id,   field_name,   field_type,   field_value, primary key (entity_id, field_name)); 

and join one-to-one entity table on f.entity_id = e.id , f.field_name = 'field1' when need field1 field? like:

select e.*, f1.field_value, f2.field_value entity e   left join entity_field f1 on f1.entity_id = e.id , f1.field_name = 'field1'   left join entity_field f2 on f2.entity_id = e.id , f2.field_name = 'field2' 

or add fields entity , live 100-200 fields in?

how hold fields in separate table:

create table entity_field (   entity_id,   field1,   field2,   field3,   field4,   ... primary key (entity_id)); 

so join on f.entity_id = e.ie? like:

like:

select e.*, f.field1, f.field2 entity e, entity_field f   e.id = f.entity_id 

i use oracle/java/hibernate seems question general design.

there no right or wrong answer , guess should employ method reduces need recompile , or remap entities.

i have personal rule this. if option dictates business rule logic or program flow should type field in main entity table , require recompilation. if storing arbitrary values such ui preferences , size info, custom images, user parameter values, or custom labels, things not require values , ideally have default values, , more importantly, system function or without values being set can put these in kind of setting table. use 2 straightforward usersettings , usersettingsconfiguration tables similar approach above.

usersetting ---------- usersettingid(pk), settingname, description category  usersettingconfiguration ------------------------ usersettingconfigurationid(pk), usersettingid (fk usersetting.usersettingid), userid (fk) value