Change database structure to store the content

[datenangebot]

We faced some issues and challenges using the native json option from the supported databases.
It is quite hard to implement all the features in all supported db systems (pgsql, maria/mysql, sqlite, oracle?).
The syntax is quite hard, completely different between the db systems and absolute not readable for bug fixing and enhancements.

At the moment we have a big technical effort to request data (get structures, permission checks, get the "real" data and statistic data like rows count). We don't exactly know how the system would behave in very large setups, but we assume a negative performance.
It would make more sense to have the technical effort on the structure changes and not on the data requests, so we like to discuss different approaches to store the data on the back end side.

Small use case as an example:
We have a list of 3 products with a title, description and price each. Would be presented like:

Name	Description	Price
Monitor	Displays what ever you need in a technical way	100€
Mouse	Lets you interact with your pc.	15€
Keyboard	Just type your ideas and feed the AI.	45€

New approach 1

Each customer table has it's own table in the db, the same columns etc. So querying data is quite simple. But we have to manipulate the database structure outside of the "setup and migration" context. Uncertain about sideffects here...

tables

id	title	owner	...
1	Products	admin	...

columns

id	table_id	title	type	subtype	prefix	suffix
1	1	Name	text	line
2	1	Description	text	rich
3	1	Price	number			€

rows_for_table_1

column_1	column_2	column_3
Monitor	Displays what ever you need in a technical way	100€
Mouse	Lets you interact with your pc.	15€
Keyboard	Just type your ideas and feed the AI.	45€

New approach 2

Each column type has it's own table. Is it possible to merge them in a sql way so we can filter and sort directly within one query?

tables

id	title	owner	...
1	Products	admin	...

columns

id	table_id	title	type	subtype	prefix	suffix
1	1	Name	text	line
2	1	Description	text	rich
3	1	Price	number			€

row_values_for_column_type_text

id	value	column_id	row_id
1	Monitor	1	1
2	Displays what ever you need in a technical way	2	1
3	Mouse	1	2
4	Lets you interact with your pc.	2	2
5	Keyboard	1	3
6	Just type your ideas and feed the AI.	2	3

row_values_for_column_type_number

id	value	column_id	row_id
1	100	3	1
2	15	3	2
3	45	3	3

@juliushaertl To continue our discussion and thoughts...

[datenangebot]

For approach 2

With this query we get the data in a table format as expected:

SELECT 
	row_id,
	MAX(CASE WHEN t1.column_id = 1 THEN t1.value ELSE null END) as column_1,
	MAX(CASE WHEN t1.column_id = 2 THEN t1.value ELSE null END) as column_2,
	MAX(CASE WHEN t1.column_id = 3 THEN t1.value ELSE null END) as column_3
    
FROM 
    (
    SELECT *
	FROM row_values_for_column_type_text
        WHERE table_id = 1

	UNION ALL

	SELECT *
	FROM row_values_for_column_type_number
        WHERE table_id = 1

) as t1
GROUP BY row_id;

[datenangebot]

Question to discuss: Do we need a additional table to hold all row ids with foreign keys to the values? If we do not, how can we get the "next" ID for a row?