G
G
Grigory Boev2019-09-03 01:21:29
Database design
Grigory Boev, 2019-09-03 01:21:29

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:

spoiler

+----+----------+---------+-------------+
| id | ObjectId | Value   | FieldName   |
+----+----------+---------+-------------+
| 1  | 1        | 1запись | description |
+----+----------+---------+-------------+
| 2  | 1        | 1       | val         |
+----+----------+---------+-------------+
| 3  | 2        | 2       | val         |
+----+----------+---------+-------------+
| 4  | 2        | 2запись | description |
+----+----------+---------+-------------+


and request for 1 field
(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')

which issues
spoiler

+----+-------------+
| id | description |
+----+-------------+
| 1  | 1запись     |
+----+-------------+
| 2  | 2запись     |
+----+-------------+


but it needs to be
spoiler

+----+-------------+-----+
| id | description | val |
+----+-------------+-----+
| 1  | 1запись     | 1   |
+----+-------------+-----+
| 2  | 2запись     | 2   |
+----+-------------+-----+

Answer the question

In order to leave comments, you need to log in

4 answer(s)
S
Stalker_RED, 2019-09-03
@Stalker_RED

It looks like you are trying to invent EAV.
1 , 2

C
coderisimo, 2019-09-03
@coderisimo

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

A
Andrey, 2019-09-03
@VladimirAndreev

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 .

A
Andrey Skorzhinsky, 2019-09-04
@AndyKorg

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 question

Ask a Question

731 491 924 answers to any question