-3

We're rewriting an old application that uses a single table to manage all of its primitive data types using a key/value format. This table contains thousands of miscellanous items, but here's one example of how it stores OS/OS versions:

FirstField   FirstValue   SecondField         SecondValue
System OS    CentOS       System OS Version   5.5
System OS    CentOS       System OS Version   5.6
System OS    CentOS       System OS Version   Other
System OS    NT           System OS Version   NT 3.51
System OS    NT           System OS Version   NT 4.0
System OS    NT           System OS Version   NT 4.0 - SP5
System OS    NT           System OS Version   NT 4.0 - SP6a

They're mostly for dropdowns and it works like this:

  • System OS dropdown is bound with all FirstValue items where FirstField = 'System OS'.
  • When an OS is selected, an OS Version dropdown is populated with SecondValue items based on what is selected for System OS.

The stored data itself is non-normalized, so those actual string values are stored to the records themselves. To normalize the data, I'd have a separate table called OperatingSystem with an ID and an OperatingSystemVersion table with its own ID and an OS_ID FK back to OperatingSystem.

With the hundreds of different primitive data types available, this would be a ton of extra tables in the database.

Is there a standard, best-practice approach to handling large amounts of primitive data types such as this while also keeping the data normalized?

Update

Here's the solution I ultimate used. It's very intricate and works well in keeping everything normalized while also supporting infinite hierarchical tiers.

Table: DATAFIELD

╔════╦═════════════════════════════╦════╗
║ Id ║          FieldName          ║ Fk ║
╠════╬═════════════════════════════╬════╣
║  1 ║ SwApplication               ║    ║
║  2 ║ SwApplicationManager        ║  1 ║
║  3 ║ SwApplicationManagerVersion ║  2 ║
╚════╩═════════════════════════════╩════╝

Table: DATAITEM

╔════╦═════════╦═════════════════╦════╗
║ Id ║ FieldId ║      Value      ║ Fk ║
╠════╬═════════╬═════════════════╬════╣
║  1 ║       1 ║ ABC Application ║    ║
║  2 ║       1 ║ DEF Application ║    ║
║  3 ║       2 ║ ABC Manager1    ║  1 ║
║  4 ║       2 ║ ABC Manager2    ║  1 ║
║  5 ║       2 ║ DEF Manager     ║  2 ║
║  6 ║       3 ║ 5.4.0           ║  3 ║
║  7 ║       3 ║ 5.4.8           ║  4 ║
║  8 ║       3 ║ 5.5.1           ║  4 ║
║  9 ║       3 ║ 2.1.0.0         ║  5 ║
║ 10 ║       3 ║ 2.1.2.0         ║  5 ║
║ 11 ║       3 ║ 2.1.3.0         ║  5 ║
╚════╩═════════╩═════════════════╩════╝

Using the above as an example, our current application would store SwApplication, SwApplicationManager, and SwApplicationManagerVersion as separate fields.

Using this new method, I will only have a single field called SwApplication (look at it as a categorical classification of what the data ultimatately represents rather than a literal data value itself). In this field, I will store the ID of the lowest child in the relationship tree. Here's how it works:

SwApplication > 1 to Many > SwApplicationManager > 1 to Many > SwApplicationManagerVersion

Let's say I am using ABC Application with ABC Manager1 version 5.4.0. All I will store in the database field is ID of 6 which references DATAITEM.ID. With this reference, I know that the parent item is ABC Manager1 because the version's FK points back to ID 3. I now know that the application is ABC Application because the manager's FK points back to ID 1. Each DATAITEM also has a reference back to its field if I need it.

What's great about this design is that I can store an infinite hierachy of tiers. I can not only add new tiers at the bottom, but also to the top. As the answerer also mentioned, a recursive CTE would also work with this design.

I am using Entity Framework for this, so it will handle all the tedious joins. I would probably not recommend this design to anyone who is not using an ORM or otherwise has to hardcode their SQL. Still, you can't beat the infinite hierarchical support here, and the normalization of data is an added plus.

  • What problem are you trying to solve by redesigning this table? Thousands of pieces of data is not a "large amount". – Max Vernon Jan 31 '17 at 16:29
  • @MaxVernon It's not the pieces of data, but rather the number of types. In my example, System OS is a type and System OS Version is a subtype (so a type in an of itself). We have 130 total types which would mean that many individual tables if we were to normalize the data. When I said thousands in my OP, that addressed the quanity of all pieces of primitive data. I really should have counted the number of types themselves. Edit: Will add details as to why I want to redesign – oscilatingcretin Jan 31 '17 at 16:40
  • 1
    I guess my point is that unless you need to store many pieces of meta-data about each type/subtype, then it looks like your current design is not that bad. It's a typical menu storage system, at least in my experience. – Max Vernon Jan 31 '17 at 16:43
  • You could normalize the data, but for the quantity of items in your question, that seems like overkill to me. Unless you were running this on an extremely resource-limited platform, where every.single.byte.counts. – Max Vernon Jan 31 '17 at 16:48
  • @MaxVernon I want to normalize it so that I don't repeat the data on the compex types that reference it. For example, we might have a thousand system records that use OperatingSystemXYZ. I don't want to store that string in a thousand separate records, so that would require me to redesign the above table to incorporate IDs that I could use as FKs. I suppose I could give the table an ID field, but then multiple other tables would have Fk references back to this same lookup table (I hope I am explaining my issue clear enough) – oscilatingcretin Jan 31 '17 at 16:54
2

With the intention of describing how you could do this with a hierarchical key/value pairs table, supporting foreign keys, I've built the following tiny test-bed of code that allows exploring how the structure might work.

Do this in tempdb, to avoid killing anything "interesting":

USE tempdb;
IF OBJECT_ID(N'dbo.OSDetails') IS NOT NULL
DROP TABLE dbo.OSDetails;
IF OBJECT_ID(N'dbo.KeyValuePairs') IS NOT NULL
DROP TABLE dbo.KeyValuePairs;

Hierarchical table of key/value pairs:

CREATE TABLE dbo.KeyValuePairs
(
    KVP_ID int NOT NULL
        CONSTRAINT PK_KeyValuePairs
        PRIMARY KEY CLUSTERED
    , KeyValue varchar(100) NOT NULL
    , Parent_KVP_ID int NULL /* optional reference back to parent key */
        CONSTRAINT FK_KeyValuePairs_Parent_KVP_ID
        FOREIGN KEY 
        REFERENCES dbo.KeyValuePairs(KVP_ID)
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Table with details around Operating Systems including Release Date and End-of-Life and a foreign key reference back to the key/value pairs table:

CREATE TABLE dbo.OSDetails
(
    OS_ID int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT PK_OSDetails
        PRIMARY KEY CLUSTERED
    , ReleaseDate date NOT NULL
    , EndOfLife date NULL
    , KVP_ID int NOT NULL
        CONSTRAINT FK_OSDetails_KVP_ID
        FOREIGN KEY 
        REFERENCES dbo.KeyValuePairs(KVP_ID)
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Insert some sample data into the key/value pairs table in a hierarchical fashion. The last two items are sub-values of the 'Windows 10' value:

INSERT INTO dbo.KeyValuePairs (KVP_ID, KeyValue, parent_KVP_ID)
VALUES (1, 'Windows 10', NULL);

INSERT INTO dbo.KeyValuePairs (KVP_ID, KeyValue, parent_KVP_ID)
VALUES (2, 'Build 1511', 1);

INSERT INTO dbo.KeyValuePairs (KVP_ID, KeyValue, parent_KVP_ID)
VALUES (3, 'Build 15014', 1);

Insert some details into the OSDetails table with links into the key/value pairs table:

INSERT INTO dbo.OSDetails (ReleaseDate, EndOfLife, KVP_ID)
VALUES (N'2015-07-09', N'2020-10-13', 1);

INSERT INTO dbo.OSDetails (ReleaseDate, EndOfLife, KVP_ID)
VALUES (N'2016-07-01', N'2021-10-13', 2);

INSERT INTO dbo.OSDetails (ReleaseDate, EndOfLife, KVP_ID)
VALUES (N'2017-01-20', N'2022-10-13', 3);

Build a recursive CTE showing one way to display results combining data from both tables:

;WITH recursive_cte AS 
(
    SELECT kvp1.KVP_ID
        , kvp1.KeyValue
        , kvp1.Parent_KVP_ID
        , Level = 1
    FROM dbo.KeyValuePairs kvp1
    WHERE kvp1.Parent_KVP_ID IS NULL
    UNION ALL
    SELECT kvp2.KVP_ID
        , kvp2.KeyValue
        , kvp2.Parent_KVP_ID
        , Level = rc.Level + 1
    FROM dbo.KeyValuePairs kvp2
        INNER JOIN recursive_cte rc ON kvp2.Parent_KVP_ID = rc.KVP_ID
)
SELECT ParentOS = (SELECT kvp.KeyValue FROM dbo.KeyValuePairs kvp WHERE kvp.KVP_ID = rc.Parent_KVP_ID)
    , rc.KeyValue
    , osd.ReleaseDate
    , osd.EndOfLife
FROM dbo.OSDetails osd
    INNER JOIN recursive_cte rc ON osd.KVP_ID = rc.KVP_ID
WHERE osd.OS_ID = 2;

The results of the query:

+------------+------------+-------------+------------+
|  ParentOS  |  KeyValue  | ReleaseDate | EndOfLife  |
+------------+------------+-------------+------------+
| Windows 10 | Build 1511 | 2016-07-01  | 2021-10-13 |
+------------+------------+-------------+------------+

If recursive CTEs aren't your thing, you can get simpler, and do something like this to obtain values for populating drop-downs, etc:

SELECT ParentOS = kvp2.KeyValue
    , ChildOS = kvp.KeyValue
    , osd.ReleaseDate
    , osd.EndOfLife
FROM dbo.OSDetails osd
    INNER JOIN dbo.KeyValuePairs kvp ON osd.KVP_ID = kvp.KVP_ID
    LEFT JOIN dbo.KeyValuePairs kvp2 ON kvp.Parent_KVP_ID = kvp2.KVP_ID
WHERE osd.OS_ID = 3;
+------------+-------------+-------------+------------+
|  ParentOS  |   ChildOS   | ReleaseDate | EndOfLife  |
+------------+-------------+-------------+------------+
| Windows 10 | Build 15014 | 2017-01-20  | 2022-10-13 |
+------------+-------------+-------------+------------+
  • So this is interesting. You store all parent-child relationships in a single table that joins on itself. I do like this idea. I will see how this works with EntityFramework. – oscilatingcretin Feb 2 '17 at 13:05
0

There are a couple of things that can reduce the number of primitive tables.

One is to group things Logically at a "near-primitive" level. Your example includes OS, OS version, (and could be broken down to OS patch level to capture the Service Packs...). You could build an OS table that includes these primitives grouped together logically so you'd have an OperatingSystems table with OperatingSystemKey, OSCompany, OSname, OSVersion, and OSPatchNumber and whatever related columns. This table would then have link to your master data table via the OperatingSystemKey column only. Not strictly 3rd normal form, but it may work better than a long chain of interconnected primitive tables.

Another option is to have a Keys table and a KeyValues table. The Keys table would include things like:

Key KeyName
1   "OS"
2   "OS Version"
3 "OS Company"
4 "Primitive type 4"
5 "Primitive type 5"

Then a KeyValues table like

KvKey Key Value
1   1   CentOS
2   1   NT
3   1   iOS
4   2   5.5
5   2   5.6
5   2   NT 3.51
6   2   NT 4.0

and so on.

In your master table, you would see something like:

 Select ... kv.Value ...
 from MasterData md
 Left Join KeyValue kv on kv.kvKey = md.KvKey

But to get a list of all operating systems, you would use

 Select Value from KeyValues where Key = 1
-3

Could have single lookup table with ID

Lookup 
  ID 
  Key 
  Value 

Table PK would be ID, Key with a unique constraint on ID, Value
Just use the ID in the join

from valuesHierarchyTable VHT
join lookup as lookup11
      on ID = 11 
     and key = VHT.key11
join lookup as lookup12
      on ID = 12 
     and key = VHT.key12

And then a FieldName table with (same) ID as the PK

You could also add ID to valuesHierarchyTable and then would have ID11, and ID12 in that table. Then you don't need to hard code ID in the join.

Two possible master tables depending on you you want to manage

key11
key12 
composite PK 

ID1 
Key1 
ID2 
Key2 
still a composite PK 

In the first the app is explicitly aware of what is in two columns. Enforcement of DRI is not clean in a lot of DB with this approach.

In the second you the relationship in the table but at the cost of 2 extra columns. But you don't have to hard code and ID in the query and can enforce DRI.

  • How is that different from what he already has, aside from the column names? – Max Vernon Jan 31 '17 at 16:32
  • @MaxVernon It would be a single lookup table rather than multiple – paparazzo Jan 31 '17 at 16:33
  • From the OP: "old application that uses a single table " – Max Vernon Jan 31 '17 at 16:34
  • 1
    @Paparazzi and where are the hierarchy relationships stored in your design? – ypercubeᵀᴹ Jan 31 '17 at 16:49
  • 1
    @Paparazzi - please clarify your answer - perhaps with some detailed code for exactly what you mean. As it is, this answer is not clear. – Max Vernon Jan 31 '17 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.