database design (custom attributes depending on a another attribute) - MySQL or other

Question

I have a question concerning the modeling of my database.

I have a simple database containing items/systems (see example below)

There is a main table with the system id, name, description, and a couple of other fields/attributes for each system.

Each system can be of a certain "system type" (for example a server, a router, an encryptor etc.).

I've put these "types" in a separate table with codes (systemtype_code in the example below).

What I want to achieve (it's already working but not flexible) is :

• I would like custom "values/attributes" for each system type.

For example, if the system type is "SSR", I would like to have fields such as "rack", "rack location", etc. If the system type is "RTR", I would like to have fields such as "number of ports", "syslog server (listbox containing other items)", etc..

You get it I think.

I manage to do it with something similar to the example below (some fields can be used for many system types naturally), but it's very simple. It works perfectly, but it's limited as the field can just be of one type (TEXT, VARCHAR or other stuff like that).

So my questions are :

• Is my method the good one ? (it's going to do huge link tables at the end)
• How can I improve it so that it allows some custom fields to be of different types (e.g a listbox etc. for example)

Thanks

Here is an example of what I currently have :

--------------------
table : systems
--------------------
sid
name
description
building_code
responsible_user
systemtype_code

--------------------
table : systemtypes
--------------------
systemtype_code
systemtype_name

--------------------
table : systemattrs
--------------------
systemattr_id
systemattr_name

--------------------
table : systemattrvalues
--------------------
sid
systemattr_id
systemattr_name

--------------------
table : l_systemattrs_systemtypes
--------------------
systemattr_id
systemtype_code

To get the attributes related to a system, I can simply make a query like :

                   SELECT   a.systemattr_name,
                                v.systemattr_value
                       FROM     systemattrs a, 
                                systemattrvalues v,
                                systemtypes t,
                                l_systemattrs_systemtypes l
                       WHERE    v.sid = 'MY_DESIRED_SYSTEM_ID'
                       AND      l.systemattr_id = a.systemattr_id
                       AND      l.systemtype_code = t.systemtype_code
                       AND      v.systemattr_id = a.systemattr_id

It works nicely, but is not exactly what I want.

Let me know if you want a database design schema to understand my question a little better.

Answer 1

As others have commented, you've reinvented a design known as "Entity-Attribute-Value"or EAV. There are many, many conversations about this on Stack Overflow; overall, the recommendation is not to use this.

The second problem you've bumped into is storing polymorphic data types in a relational database. This too has been discussed on Stack Overflow.

If you know the attributes you want to store for each subtype, there are 3 common ways of storing polymorphic data in a relational database (see link above); if you do not know all the attributes, you might use your database's support for JSON or XML documents to store the extended attributes.

Both options are much better than EAV in most scenarios - imagine a query for all RTRs with more than 8 ports, not in use as a syslog server, installed in the last 3 months.

Answer 2

You clarified in comments that you already use a relational database in most of your other application code. This is common. A relational database is great when you need consistent data structure enforced by a schema. It's typical that only a small part of your application needs the "flexible" data structure.

So you can continue to use a relational database, just choose one that supports a semi-structured data type. In other words, use conventional columns for the attributes common to all of your types, and use a JSON column for the optional, type-specific attributes.

See for example MySQL support for JSON:

• https://dev.mysql.com/doc/refman/5.7/en/json.html
• https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

This should give you the best of both worlds, using relational along with semi-structured in the same database.

I also posted a summary of other options in my answer to How to design a product table for many kinds of product where each product has many parameters or my presentation Extensible Data Modeling with MySQL.

EAV (which your design resembles) should be a last resort. It's possible to use this, but it's fundamentally non-relational, and you'll end up writing a lot of complicated SQL and application code to work around it. Read https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/

Answer 3

You are halfway between a conventional normalized data design and an entity-attiribute-value based structure (where an objects attributes are not named columns in a table, but named rows).

The latter (like a NoSQL daabase) allows for easily accomodating different sets of attributes, but makes it difficult to enforce rules about the structure the data and to write complex queries (e.g. what are the rack locations of the devices running CentOS 6.7)

Answer 4

For these kind of use cases better to go with NoSql Databases. My suggestion is to use MongoDB for easy data management with different type of attribute and different attribute name.

MongoDB stores the data as json format so there is no restriction of attribute name and type.