A
A
AntoxaVolkov2017-03-27 08:40:22
MySQL
AntoxaVolkov, 2017-03-27 08:40:22

How to organize the database structure for electronic journals?

Hello!
I need to create ezines (tables) with the following features:

  1. Create a new journal
    1. Journal title
    2. Creating fields
    3. Type selection for each field
    4. Ability to link a log field with a field in other logs under certain conditions
    5. And etc.

  2. Editing
  3. Removal
  4. Logging (filling rows)

So far I've found two solutions. First, create three tables: journals(id, name), fields(id, journal_id, title, type, etc.), rows (id, journal_id, json c with row data {field_id:data,field_id:data}) .
Second, each new log, new table, and field properties are stored in a separate "fields" table (id, name_table, field_name, type, etc.) Please tell me
which solution is better and why, or maybe there is a better solution.
There can be dozens of logs, hundreds of fields, and tens of thousands of rows. And if applications use other departments, then there are hundreds of logs, tens of thousands of fields, and millions of rows. Therefore, performance is extremely important.
I develop on Laravel + Mysql.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Adamos, 2017-03-27
@Adamos

I would start thinking with the question "why is a relational database here and what is actually required of it?".
If you don't know the answer, take a closer look at the databases specially designed for storing documents.

D
Draconian, 2017-03-27
@Draconian

Theoretically, json in a separate field breaks data atomicity. If you're going to store them this way, then you probably don't need a relational database.
The second option for relational databases is quite usable, the load depends on the hardware on which the DBMS + caching, indexes will work, that's all.
If you want to follow the relational path, in my opinion, it is better to do this - store journals (PK journal_id, name) in one table, journal fields in another (FK journal_id), and field values ​​without json (FK field_id) in the third. Relationships are one-to-many everywhere - one log has many fields, one field has many values.
Your version is also working, but there is a small minus - adding fields will be done through alter table, as far as I understand.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question