Table structure, single-field row VS all-data-in-one

Question

Question about table structure.

Here is a small scenario to introduce the question: Imagine that you want to store class objects (let A) in a table.

You have two possible table structures:

Structure A: "one field per row": id (int), name (text), credit (int), birthday (date). Structure B: "all data in one row": id (int), data (bigtext). 

Consider the following:

• NEVER run queries that filter / sort the name / credit / birthday fields
• Before editing a field you want to load an object
• Field names / credit / birthday do not have parameters / modifiers (keys / unique / ...)

What is the difference between the two table structures?

.

Specifically, I am making a PHP / SQLITE application that - at some point - should store objects in a database. I would like to be able to easily add a db-saved class without having to edit my db schema every time I do this. Using "structure B" would allow me to do this. It may look dirty, and you may have been taught that you need to have beautifully printed lines .. but why not?

Is the main advantage of "structure A" only the effectiveness of filters and select updates?

Answer 1

You are talking about processing all your data integrity in a business layer / web application, which is quite acceptable these days.

Instead of using the table structure at all, why not just save the JSON object? This way you don't have to worry about circuit changes, and you can just serialize / deserialize the object for use with your interface.

Perhaps consider using a key / value store (NOSQL solution) for something similar, although there will actually be enough database.

To answer your question, the difference lies in the possibility of querying the fields, checking data, maintaining data integrity, etc., while in structure B you process all this outside the database in the application.

As for your supposed limitation that you cannot request an object, MapReduce will allow you to run aggregate requests on your JSON data.

Go to option B if you need flexibility and you do not need other advantages provided by a structured database, go for option A if you want to check your data in the database and make it easier to query it.

The benefits of structure A are data integrity rules that are close to data and the ability to easily request your data in a variety of ways.

The advantages of structure B are extensibility and scalability - you can easily change your data structures in the application, and also, if you need to scale, you can easily split your data horizontally.

Answer 2

Never do an example with all data in one field with a relational database. This is WAYY more effort and less flexibility.

If in a year you want to create another project that will access the database, you will have to duplicate a lot of efforts to check, extract, etc., whereas if everything were in a separate column, it would be much easier.

You said: “NEVER execute queries filtering / sorting the name / credit / birthday fields” I very much doubt that this will work. One thing that you quickly learn in software development is statements such as “about what could never happen” that will always come back to bite you in the ass.

If you really want to go with approach b) at least take a look at nosql. I don’t know much about this, but it will be better suited for your project than RDBM if this is the route you want to take

Answer 3

No. What about for example security type? In structure B, what prevents me from declaring my birthday as “12th of Never,” or “Red,” or “3.1415”?

Answer 4

Why use a relational database? The benefits of using separate fields are usually superior to the benefits of simply grouping all the data in one field. Why don't you want your data to be already split and printed correctly?

In any case, please refer to the first normal form (as well as the second and third), which prohibits non-atomic pieces of data. If your code is part of any professional work, you should probably follow it.