Design: Fitting variable hierarchy of text files into a relational db

database-designnosql

I have a large data corpus (several TB) made up of data gathered through customer support queries. I've been asked to set up a database to make this data more accessible, but as I'm kind of new to database design, I'm stuck on what may be a fairly trivial design decision.

One of the primary components of this data is a copy of the software configuration for each user's install. The configuration consists of N plain text files, in a fixed directory structure, but each directory can contain a number of different config files depending on how the user has configured their install.

Example hierarchy:

conf
|-- general.cfg
|-- instances
|   |-- exchange_mediator.cfg
|   |-- primary.cfg
|   `-- sip.cfg
|-- settings.cfg
`-- users
    |-- admin
    |   `-- admin_setup.cfg
    |-- admin.cfg
    |-- restricted_user.cfg
    `-- terry.cfg

As mentioned above, different installs may have a different number of files in any given directory, and they may be named differently also. I'm not sure how to fit the contents of these files (just a simple text blob) into a relational database so that it's easy to search for certain strings in the contents of some or any of these files.

Example queries might be:
+Show me all config files in "instances" containing the string "restricted"
+Show me all support instances with a "user" config file named "admin.cfg"

Any advice would be much appreciated!

Note:
In terms of the specific database I'm using, I'm still researching that. I've looked at NoSQL db's such as elasticsearch and mongodb, as it seems like creating searchable JSON objects would be a great way of handling this. I'd rather avoid elasticsearch, as their documentation recommends keeping a separate database to store the data reliably (I'd rather avoid that). I've heard bad things about mongodb in terms of scalability, particularly if you have a large number of indexed keys.

For a more traditional-style SQL db, I'm looking at Amazon RDS or Redshift. Redshift seems to be easy to deploy, and quite capable of handling the amount of data I have, but as it doesn't support datatypes like arrays and structures, I'm not sure how my data could be transformed to fit into a series of tables.

Many thanks for having read this far!

Toby

Best Answer

You could store the text file data in a table like this:

config_files
------------
  ID (PK)
  name
  parent_dir_id (FK to config_dirs.id) 
  content

config_dirs
-----------
  ID (PK)
  name
  original_user (key to the table that stores the original user data)
  parent_dir_id (key to config_dirs.ID, needs to be nullable to indicate a root directory)

Populating this structure, you could do something like this:

config_dirs
-----------
ID    | name       | parent_dir_id | original_user
------+------------+---------------+--------------
1     | conf       | (null)        | Toby Derius
2     | users      | 1             | Toby Derius
3     | admin      | 2             | Toby Derius
4     | bob        | 2             | Toby Derius

config_files
------------
ID    | name       | parent_dir_id   | content
------+------------+-----------------+--------
1     |general.cfg | 1               | This is the general config...
2     |admin.cfg   | 3               | admin's config...

This could allow you to query the directory structure and the file contents for specific users. Storing large amounts of plain text is not always a great idea, in terms of performance, so you might also want to consider storing the contents in a separate table file_contents and then referencing the file_contents from config_files. Also look into fill-text search engines.

Related Question