Table structure, one-field-per-row VS all-data-in-one
A question about table structure.
Here is a small scenario to introduce the question: Imagine you want to store objects of a class (let A) in a table.
You have two possible table structure to do so:
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:
- You will NEVER execute requests filtering/sorting the fields name/credit/birthday
- Before editing a field, you want to load the object
- Fields name/credit/birthday have no options/modifiers (keys/unique/...)
What is the difference between theses two table structure ?
Concretely, I'm doing a PHP/SQLITE app that - at one point - needs to store objects in database. I'd like to be able to easily add db-stored-class without having to edit my db scheme each time I do so. Using "structure B" would allow me to do so. It may look dirty and yea you've probably be teached that you need to have nicely typed rows.. but why not ?
Isn't the main advantage of "structure A" only the effectiveness of select filters and updates ?
You're talking about handling all your data integrity in the business layer/web app which is totally acceptable these days.
Rather than using a table structure at all, why not just store a JSON object? That way you don't have to worry about schema changes and you can just serialize/deserialize the object for use with your front end.
Maybe consider using a key/value store (a NOSQL solution) for something like this although any database will suffice really.
To answer your question - the difference is in being able to query against the fields, validate data, maintain data integrity etc whereas in structure B you're handling all of this outside of the database in the application.
Regarding your perceived limitation about not being able to query against the object, MapReduce will allow you to run aggregate queries over your JSON data.
Go for option B if you need the flexibility and don't need the other benefits a structured database provides, go for option A if you want to validate your data in the database and run queries more easily over it.
The advantages of structure A are data integrity rules close to the data, and ability to easily query your data in many different ways.
The advantages of structure B are extensibility and scalability - you can change your data structures easily in the application and also if you need to scale you can horizontally partition your data easily.
Never ever ever do example two with all data in one field with a relational database. It is WAYY more effort and has way less flexibility.
If in a year from now you want to create another project that access the database you will have to duplicate alot of the validation, extraction, etc effort, whereas if everything was in a seperate column it would be much easier.
You said "You will NEVER execute requests filtering/sorting the fields name/credit/birthday" I highly doubt it will work that way. One thing you learn quickly in software development is statements like "oh that could never happen" will always come back to bite you in the ass
If you really want to go with the approach of b) atleast look into nosql. I don't know much about it but it will be better suited for your project than a RDBM if that is the route you want to take
No. What about, for example, type safety? In structure B, what stops me declaring my birthday to be "the 12th of Never", or "Red", or "3.1415"?
Why use a relational database at all? The advantages of using separate fields usually outweigh those of just grouping all data in one single field. Why wouldn't you want your data already split and typed properly?
In any case, please refer to the First Normal Form (as well as the second and third) which forbids non-atomic pieces of data. If your code is part of some professional work, you should follow probably them.