Search code examples
sqldatamodel

What kind of data model models function parameters and results?


The following table...:

CREATE TABLE v (
    height int,
    width int,
    depth int,
    volume int,
    PRIMARY KEY (height, width, depth);
)

... could be used to store the inputs and outputs of a function of 3 variables named volume: volume(height, width, depth) = height * width * depth.

What kind of data model am I using here? Is it Entity-Attribute-Value?


Solution

  • You appear to be mathematically oriented. When Codd(Date) introduced the relational model, he(they) reshaped the existing mathematical language to the new field.

    In your case: value could be a function of the three parameters: val = f(h,w,d). But for "relations" (database tables) things are slightly different: the function is only defined if {h,w,d} actually EXISTS in the table. In mathematics, the function is defined over the entire R3 domain. In relational algebra, the key of a table ({h,w,d}) is defined over a more restricted domain (or the product of a set of domains). Most of the DBMS/SQL world is involved with these restrictions. (constraints, domain constraints, whatever) The UNIQUE constraint is perhaps the most fundamental constraint: it guarantees that there is at most one tuple with a particular {h,w,d}. As a consequence there is only one function value. DBMS people call the non-key fields of a relation "functionally dependant" on the FK ({h,w,d}}: given the set of keys, there can be at most one row that corresponds to it (and at most one "function value")

    EAV is just a class of data models to "simulate" that an object has a variable number of attributes, without changing the definitions of the tables involved. But, on the table level, it just means adding an extra table (actually two) with the attibutes and the values. Data modelling is only topology in disguise.