A way to store array type data into single database table?

Question

I am having a input JSON which I need to feed into a database. We are exploring on whether to normalize or not our database tables.

Following is the structure for the input data (json):

"attachments": [
        {
            "filename": "abc.pdf",
            "url": "https://www.web.com/abc.pdf",
            "type": "done"
        },
        {
            "filename": "pqr.pdf",
            "url": "https://www.web.com/pqr.pdf",
            "type": "done"
        },
               ],

In the above example, attachments could have multiple values (more than 2, upto 8).

We were thinking of creating a different table called DB_ATTACHMENT and keep all the attachments for a worker down there. But the issue is we have somewhat 30+ different attachment type array (phone, address, previous_emp, visas, etc.)

Is there a way to store everything in ONE table (employee)? One I can think of is using a single column (ATTACHMENT) and add all the data in 'delimited-format' and have the logic at target system to parse and extract everything.. Any other better solution?

Thanks..

Answer 1

Is there a way to store everything in ONE table (employee)? One I can think of is using a single column (ATTACHMENT) and add all the data in 'delimited-format' and have the logic at target system to parse and extract everything.. Any other better solution?

You can store the data in a single VARCHAR column as JSON, then recover the information in the client decoding this JSON data.

Also, there are already some SQL implementations offering native JSON datatypes. For example:

mariaDB: https://mariadb.com/kb/en/mariadb/column_json/

mySQL: https://dev.mysql.com/doc/refman/5.7/en/json.html

Answer 2

Database systems store your data and offer you SQL to simplify your search requests in case your data is structured.

It depends on you to decide whether you want to store the data structured to benefit from the SQL or leave the search requester with the burden of parsing it.

Answer 3

It very much depends on how you intend to use the data. I'm not totally sure I understand your question, so I am going to rephrase the business domain I think you're working with - please comment if this is not correct.

• The system manages 0..n employees.
• One employee may have 0..8 attachments.
• An attachment belongs to exactly 1 employee.
• An attachment may be one of 30 different types.
• Each attachment type may have its own schema.

If attachments aren't important in the business domain - they're basically notes, and you don't need to query or reason about them - you could store them as a column on the "employee" table, and parse them when you show them to the end user.

This solution may seem easier - but don't underestimate the conversion logic - you have to support Create, Read, Update and Delete for each attachment.

If attachments are meaningful in the business domain, this very quickly breaks down. If you need to answer questions like "find all employees who have attached abc.pdf", "find employees who do not have a telephone_number attachment", unpacking each employee_attachment makes your query very difficult.

In this case, you almost certainly need to store attachments in one or more separate tables. If the schema for each attachment is, indeed, different, you need to work out how to deal with inheritance in relational database models.

Finally - some database engines support formats like JSON and XML natively. Yours may offer this as a compromise solution.