Answer the question
In order to leave comments, you need to log in
How to make a table with an arbitrary number of fields?
Tell me, please, how to make a table in a relational database, so that later you can add fields without changing the structure of the table? In this case, you need to keep within the minimum number of tables. As I understand it, you can store in a couple of tables (or is even one enough?):
https://app.dbdesigner.net/designer/schema/265467
in ObjectId we store the record id of the "large" table, FieldName and Value store the name of the "large" table " table and its meaning.
How then to get it with a query and get a two-dimensional table with all the described fields and values in them?
All I've figured out so far:
+----+----------+---------+-------------+
| id | ObjectId | Value | FieldName |
+----+----------+---------+-------------+
| 1 | 1 | 1запись | description |
+----+----------+---------+-------------+
| 2 | 1 | 1 | val |
+----+----------+---------+-------------+
| 3 | 2 | 2 | val |
+----+----------+---------+-------------+
| 4 | 2 | 2запись | description |
+----+----------+---------+-------------+
(SELECT o.id,f.value as 'description' from test1.dbo.Objects as o
JOIN test1.dbo.Fields f ON o.id = f.ObjectId where FieldName like 'description')
+----+-------------+
| id | description |
+----+-------------+
| 1 | 1запись |
+----+-------------+
| 2 | 2запись |
+----+-------------+
+----+-------------+-----+
| id | description | val |
+----+-------------+-----+
| 1 | 1запись | 1 |
+----+-------------+-----+
| 2 | 2запись | 2 |
+----+-------------+-----+
Answer the question
In order to leave comments, you need to log in
table_1 : id, title
table_2: id, field, value - id refers to the first table
select* from table_2 where id = 1 // get all fields associated with the first record in table 1
select* from table_2 where id = 1 AND field=' overweight' // get the overweight field for the first record in table 1
If you have a clear understanding of the structure of tables, then:
1. The table has a physical primary key
2. The table has physical fields for foreign keys
3. The table has physical fields for main indexes
4. The table has a json field for all other data .
If a task with an "infinite schema" arises, then either the task is not for a relational database, or the data model needs to be revised.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question