我需要一些帮助来优化我的数据库模式

Here's a layout of my data:

以下是我的数据布局:

Heading 1:
   Sub heading
   Sub heading
   Sub heading
   Sub heading
   Sub heading

Heading 2:
   Sub heading
   Sub heading
   Sub heading
   Sub heading
   Sub heading

Heading 3:
   Sub heading
   Sub heading
   Sub heading
   Sub heading
   Sub heading

Heading 4:
   Sub heading
   Sub heading
   Sub heading
   Sub heading
   Sub heading

Heading 5:
   Sub heading
   Sub heading
   Sub heading
   Sub heading
   Sub heading

These headings need to have a 'Completion Status' boolean value which gets linked to a user Id.

这些标题需要有一个“完成状态”布尔值，该值被链接到用户Id。

Currently, this is how my table looks:

目前，我的表是这样的:

id  |  userID  |  field_1  |  field_2  |  field_3  |  field_4  | etc...
-----------------------------------------------------------------------
1   |     1    |    0      |     0     |     1     |     0     |
-----------------------------------------------------------------------
2   |     2    |    1      |     0     |     1     |     1     |

Each field represents one Sub Heading. Having this many columns in my table looks awfully inefficient...

每个字段表示一个子标题。在我的表中有这么多列看起来非常低效……

How can I go about optimizing this? I can't think of any way to neaten it up :/

我该如何优化它呢?我想不出有什么办法能把它整理好。

3 个解决方案

#1

Don't use boolean values but simple relations:

不要使用布尔值，而是使用简单的关系:

table completion_status

表completion_status

id user_id field_id

1  1       3
2  2       1
3  2       3
4  2       4
...

From that it's easy to see that user 1 has completed field 3, and user 2 fields 1, 3 and 4.

从这里可以很容易看出用户1已经完成了字段3，用户2字段1、3和4。

This way you don't have to change your database schema whenever you want to change the amount of fields.

这样，当您希望更改字段的数量时，就不需要更改数据库模式。

#2

You could have the sub-headings in a different table, and then link that table to the first table.

您可以将子标题放在另一个表中，然后将该表链接到第一个表。

id  |  userID  |  field_id | 
-----------------------------
1   |     1    |    0      | 
-----------------------------
2   |     2    |    1      |

where table field looks like this

表字段是这样的

id  | field_1 | field_2   | etc..
-----------------------------
1   |     1   |    0      | etc..

#3

Why dont you make a table like this?

你为什么不做一张这样的桌子呢?

id  | User Id | Field ID | Status
---------------------------------
1   |   1     |   1      | 0   
2   |   1     |   2      | 1

You can have another master table for Fields with the field names

您可以为字段名使用另一个主表。

#1