Accessing data from SQL table containing ID, [Value Type] and Value

Question

If this question has been asked before, please share a link to that question. I've done some searching and was not able to find an answer to a question like this. This is possibly due to the difficulty I've encountered trying to word it into a query that displays the results I'm looking for.

Background:
I'm designing a database to hold some information (like databases usually do). This particular database holds, just for the purpose of this question, three tables.

The three tables store different information; one table defines the user by a unique ID and user name, the second stores the types of information that can be stored for a user and the third stores the information about the user .

Users table is defined with:

CREATE TABLE dbo.Users (
    Id          UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    [User Name] NVARCHAR(1024)   NOT NULL
);

The [User Info Data Type] table is defined as:

CREATE TABLE dbo.[User Info Data Type] (
    Id               UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    [Data Type Name] NVARCHAR(256)    NOT NULL
);

Last but not least, the [User Info] table is defined as:

CREATE TABLE dbo.[User Info] (
    Id               UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    [User Id]        UNIQUEIDENTIFIER NOT NULL, --FK from [Users].Id
    [User Info Type] UNIQUEIDENTIFIER NOT NULL, --FK from [User Info Data Dype].Id
    Value            VARBINARY(MAX)   NULL
);

Question:
I've seen this done before (at a previous job) but I'm not as advanced in SQL as I wish I was. I'd like to know how I can aggregate the data in the third table against a single record in the first table using the second table to define the header of the data in the third table.

I'll even clarify the results I'm expecting.

Let's say the 1^st table has 1 record, it's name column contains Dave.
The 2^nd table has an entry for a "Created" type.
The 3^rd table has an entry that has Dave's Id, the "Created" Id and the value (in binary) of the created date.

Let's say for arguments sake that "Dave's" record was created August 24, 2015 at 00:00h. The result of the query would yield:

User Name | Created
Dave      | 8D2AC16F443C000

The column name would be pulled from the second table and the value from the third.

I understand that this is complicated of an ask but any help would be appreciated.

Thanks in advance!

-Dave

Answer 1

This is an EAV model. As commented by Nick, you should do a research on its pros and cons. However, if you wish to pursue this model, your queries would rely so much on PIVOTs or Crosstabs. Here is an example using dynamic crosstab:

First, we create the sample data. Note, I removed the spaces from your column names.

CREATE TABLE #Users (
    Id          UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    UserName    NVARCHAR(1024)   NOT NULL
);
CREATE TABLE #UserInfoDataType (
    Id              UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    DataTypeName    NVARCHAR(256)    NOT NULL
);
CREATE TABLE #UserInfo (
    Id              UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),
    UserId          UNIQUEIDENTIFIER NOT NULL, --FK from [#Users].Id
    UserInfoType    UNIQUEIDENTIFIER NOT NULL, --FK from [User Info Data Dype].Id
    Value           VARBINARY(MAX)   NULL
);

INSERT INTO #Users(UserName) VALUES('Dave');
INSERT INTO #UserInfoDataType(DataTypeName) VALUES('Created'), ('FirstName'), ('LastName')
INSERT INTO #UserInfo(UserId, UserInfoType, Value)
    SELECT
        u.Id,
        uidt.Id,
        Value =
            CASE
                WHEN uidt.DataTypeName = 'Created' THEN CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(10),GETDATE(), 101))
                WHEN uidt.DataTypeName = 'FirstName' THEN CONVERT(VARBINARY(MAX), 'Dave')
                WHEN uidt.DataTypeName = 'LastName' THEN CONVERT(VARBINARY(MAX), 'Doe')
            END
    FROM #Users u
    CROSS JOIN #UserInfoDataType uidt

Now, for the dynamic crosstab solution:

DECLARE @sql1 NVARCHAR(2000) = '',
        @sql2 NVARCHAR(2000) = '',
        @sql3 NVARCHAR(2000) = ''

SELECT @sql1 = 
'SELECT
      u.UserName' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN i.UserInfoType = ''' + CONVERT(VARCHAR(MAX), Id) + ''' THEN CONVERT(VARCHAR(MAX), i.Value) END) AS ' + QUOTENAME(DataTypeName) + CHAR(10)
FROM #UserInfoDataType

SELECT @sql3 =
'FROM #UserInfo i
INNER JOIN #Users u ON u.Id = i.UserId
INNER JOIN #UserInfoDataType t ON t.Id = i.UserInfoType
GROUP BY i.UserId, u.UserName'

PRINT(@sql1 + @sql2+ @sql3)
EXEC(@sql1 + @sql2+ @sql3)

RESULT:

UserName Created    FirstName LastName 
-------- ---------- --------- -------- 
Dave     08/25/2015 Dave      Doe   

See it here in SEDE.