CODEWITHSUNDEEP
c#sql-servervb.net
Basuki Tjahaja
Basuki Tjahaja

Reputation: 27

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

I want to write an application are as follows:

SQL Table is 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 to get all values by [DepId]

select * from DepProperties where DepId = 123

and, In VB.Net serialize to class

Public Class Department
    Public Property DepartmentDescription As String
    Public Property DateCreated As DateTime
End Class

Public Class ExtendedDepartment
    Inherits Department

    Public Property NewerDep As Boolean
End Class

I try to use this way.

because, in the future when I add a new property to specific Department only, new class extending from the parent class contains specific properties

Rather than adding new columns in the database.

Is this a good way, and how can it do this?

Upvotes: 2

Views: 273

Answers (1)

Hannah Vernon
Hannah Vernon

Reputation: 3472

What you are proposing is commonly referred to as an Entity-Attribute-Value design (or EAV for short), and as @DanGuzman pointed out in his comment, it can become a problem for performance depending on a number of factors, including:

  • number of rows in the table
  • number of concurrent connections using the table simultaneously
  • how your client-side code is written; i.e. does it use this pattern to store all its data, or is this only for a limited number of properties?

There are a great many commercial systems built on design exactly like this that do support a large amount of concurrency, with lots of rows, and well-designed client side code.

From the Wikipedia article on EAV1:

The typical case for using the EAV model is for highly sparse, heterogeneous attributes, such as clinical parameters in the electronic medical record (EMRs), as stated above. Even here, however, it is accurate to state that the EAV modeling principle is applied to a sub-schema of the database rather than for all of its contents. (Patient demographics, for example, are most naturally modeled in one-column-per-attribute, traditional relational structure.)

Consequently, the arguments about EAV vs. "relational" design reflect incomplete understanding of the problem: An EAV design should be employed only for that sub-schema of a database where sparse attributes need to be modeled: even here, they need to be supported by third normal form metadata tables. There are relatively few database-design problems where sparse attributes are encountered: this is why the circumstances where EAV design is applicable are relatively rare. Even where they are encountered, a set of EAV tables is not the only way to address sparse data: an XML-based solution (discussed below) is applicable when the maximum number of attributes per entity is relatively modest, and the total volume of sparse data is also similarly modest. An example of this situation is the problems of capturing variable attributes for different product types.

Sparse attributes may also occur in E-commerce situations where an organization is purchasing or selling a vast and highly diverse set of commodities, with the details of individual categories of commodities being highly variable.

You may want to consider using the 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 this number to
                                               allow for the largest
                                               value you want to 
                                               support */
    , PropertyValueInt INT NULL
    , PropertyValueDate DATE NULL
    , PropertyValueDateTime DATETIME NULL
    , PropertyValueDecimal DECIMAL(20, 10) /* Adjust per your needs */
);

Note, the "value" columns are nullable and there are discrete columns for each type of data. This allows you to retain type-safety.

1 - https://en.wikipedia.org/wiki/Entity-attribute-value_model#Modeling_sparse_attributes

Upvotes: 1

Related Questions