c# - Store Properties as (name-value-pair) table? -

i want write application follows:

sql table name: (depproperties)

+----+-------+----------+------------------------+------------+ | id | depid |   type   |          name          |   value    | +----+-------+----------+------------------------+------------+ |  2 |   123 | string   | departmentdescription  | xxxx       | |  3 |   123 | datetime | datecreated            | xx/xx/xxxx | |  4 |   321 | string   | departmentdescription  | xxxx       | |  5 |   321 | boolean  | newerdep               | xx/xx/xxxx | +----+-------+----------+------------------------+------------+ 

and values [depid]

select * depproperties depid = 123 

and, in vb.net serialize class

public class department     public property departmentdescription string     public property datecreated datetime end class  public class extendeddepartment     inherits department      public property newerdep boolean end class 

i try use way.

because, in future when add new property specific department only, new class extending parent class contains specific properties

rather adding new columns in database.

is way, , how can this?

what proposing commonly referred entity-attribute-value design (or eav short), , @danguzman pointed out in comment, can become problem performance depending on number of factors, including:

• number of rows in table
• number of concurrent connections using table simultaneously
• how client-side code written; i.e. use pattern store data, or limited number of properties?

there great many commercial systems built on design support large amount of concurrency, lots of rows, , well-designed client side code.

from wikipedia article on eav¹:

the typical case using eav model highly sparse, heterogeneous attributes, such clinical parameters in electronic medical record (emrs), stated above. here, however, accurate state eav modeling principle applied sub-schema of database rather of contents. (patient demographics, example, naturally modeled in one-column-per-attribute, traditional relational structure.)

consequently, arguments eav vs. "relational" design reflect incomplete understanding of problem: eav design should employed sub-schema of database sparse attributes need modeled: here, need supported third normal form metadata tables. there relatively few database-design problems sparse attributes encountered: why circumstances eav design applicable relatively rare. encountered, set of eav tables not way address sparse data: xml-based solution (discussed below) applicable when maximum number of attributes per entity relatively modest, , total volume of sparse data modest. example of situation problems of capturing variable attributes different product types.

sparse attributes may occur in e-commerce situations organization purchasing or selling vast , highly diverse set of commodities, details of individual categories of commodities being highly variable.

you may want consider using following table design:

create table dbo.depproperties (     deppropertiesid int not null         constraint pk_depproperties         primary key clustered         identity(1,1)     , depid int not null     , propertytype varchar(50) not null     , propertyname varchar(50) not null     , propertyvaluestring varchar(247) null /* adjust number                                                allow largest                                                value want                                                 support */     , propertyvalueint int null     , propertyvaluedate date null     , propertyvaluedatetime datetime null     , propertyvaluedecimal decimal(20, 10) /* adjust per needs */ ); 

note, "value" columns nullable , there discrete columns each type of data. allows retain type-safety.

---

^{1 - https://en.wikipedia.org/wiki/entity-attribute-value_model#modeling_sparse_attributes}