Experiment with entity/attribute/value table in SQL Server using F# script.

eav.fsx

open System
open System.Data
open System.Data.Common

let exec exec sql =
    let factory = DbProviderFactories.GetFactory("System.Data.SqlClient")
    use connection = factory.CreateConnection()
    connection.ConnectionString <- "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"
    connection.Open()
    use command = factory.CreateCommand()
    command.Connection <- connection
    command.CommandType <- CommandType.Text
    command.CommandText <- sql
    exec command

let execNonQuery =
    exec (fun command -> command.ExecuteNonQuery()) >> ignore

let execReader read =
    exec (fun command ->
        let reader = command.ExecuteReader(CommandBehavior.Default)
        read reader
        )

let printColumns (reader : DbDataReader) =
    printfn "%s" <| String.Join(",", seq { for i in 0 .. reader.FieldCount - 1 -> reader.GetName(i) })

let printRows (reader : DbDataReader) =
    while reader.Read() do
        printfn "%s" <| String.Join(",", seq { for i in 0 .. reader.FieldCount - 1 -> reader.GetValue(i) })

let print =
    execReader (fun reader ->
        printColumns reader
        printRows reader
        )

let dropTable (name : String) =
    execNonQuery <| String.Format("if object_id('{0}', 'u') is not null drop table {0};", name)

let createTable name columns =
    dropTable name |> ignore
    execNonQuery <| String.Format("create table {0} ({1});", name, columns)

// Automatically drop tables after use.
let useTable name columns =
    createTable name columns |> ignore
    {new IDisposable with member d.Dispose() = dropTable name |> ignore }

let go =
    use entityTable = useTable "Entity" "ID int primary key, Name sysname"
    use attributeTable = useTable "Attribute" "ID int primary key, EntityID int foreign key references Entity(ID), Name sysname"
    use valueTable = useTable "Value" "ID int, AttributeID int foreign key references Attribute(ID), Valid date, Value sql_variant, constraint PK_Value primary key (ID, AttributeID, Valid)"
    List.map execNonQuery [
        "insert Entity values (1, 'Employee');"
        "insert Attribute values (1, 1, 'LastName');"
        "insert Attribute values (2, 1, 'Born');"
        "insert Attribute values (3, 1, 'Nationality');"
        "insert Attribute values (4, 1, 'Married');"
        "insert Value values (1, 1, '2001-01-01', 'John');"
        "insert Value values (1, 2, '2001-01-01', cast('1973-12-01' as date));"
        "insert Value values (1, 3, '2001-01-01', 'English');"
        "insert Value values (1, 4, '2001-01-01', cast(0 as bit));"
        "insert Value values (1, 3, '2009-02-02', 'Dutch');"
        "insert Value values (1, 4, '2010-03-03', cast(1 as bit));"
    ] |> ignore
    List.map print [
        "select a.Name, v.Value from Value v join Attribute a on v.AttributeID = a.ID"
        "select LastName, Born from (select a.Name , v.Value from Value v join Attribute a on v.AttributeID = a.ID) as e pivot (max(Value) for Name in (LastName, Born)) as p"
        "select sql_variant_property(Value, 'BaseType') as Type from Value"
    ] |> ignore

// TODO http://blogs.infosupport.com/blogs/marks/archive/2010/02/22/handling-eav-data-timelines.aspx