The design and thinking of the table structure of the uploaded resource file, and the reconstruction of the overall process

1. The current table design (the relationship between the topic selection and the resource), the field: plan_id indicates which topic selection ID the record belongs to, as shown in Figure 1

Figure 1

2. In the editing topic page, 2 resource files are uploaded, as shown in Figure 2

Figure 2

3. After saving the topic, open the resource table and data records, as shown in Figure 3

Figure 3

4. When designing the table structure before, the resource table is only used for the resource file under the topic selection, but now, in other pages (base settings, user settings), the resource file needs to be uploaded, as shown in Figure 4

Figure 4

Therefore, it is decided to rename the table name, plan_asset is asset, and delete the field: plan_id. Add the field of the resource ID of the material in the topic table plan, multiple uses, and the number is separated: material_asset_id, after refactoring, like in step 3, the resource table has been added 2 For records, the primary key IDs are: 13 and 14, then the fields in the topic table plan: material_asset_id are: 13, 14, so as to determine the relationship between the topic and the resource.

6. Add fields in the topic table plan: material_asset_id, the correlation relationship in the migration table plan_asset to material_asset_id, traverse the topic selection record, and check whether there is a resource record corresponding to the topic in the topic selection resource table.

Figure 5

PS e:\wwwroot\pcs-api> ./yii migrate
Yii Migration Tool (Based on Yii v2.0.15.1)

Total 1 New Migration to Be Applied:
M191016_022618_add_material_asset_id_to_plan

Apply the above migration? (Yes|No)[no]:yes
*** Applying M191016_022618_add_material_asset_id_to_plan
> add column material_asset_id string(1024) not null defaultcommentThe resource ID of the material, multiple uses, and the number is separatedafter `opinion
` to table {{%plan}} ... done (time: 0.081s)
> update {{%plan}} ... done (time: 0.005s)
> update {{%plan}} ... done (time: 0.002s)
*** Applied M191016_022618_ADD_Material_asset_id_to_plan (time: 0.112s)

1 migration was applied.

migrated up successfully.

2 13
3 14,15,16,17,18

7. After the relationship between the topic selection and the resource is migrated, rename the table name, plan_asset is asset, delete field: plan_id. Subsequent Asset tables are the public tables of the resource files of all data, and the correlation relationship is maintained by themselves in their respective data tables. as shown in Figure 6

Figure 6

PS e:\wwwroot\pcs-api> ./yii migrate
Yii Migration Tool (Based on Yii v2.0.15.1)

Total 1 New Migration to Be Applied:
M191016_070338_rename_plan_asset_to_asset

Apply the above migration? (Yes|No)[no]:yes
*** Applying M191016_070338_rename_plan_asset_to_asset
> rename table {{%plan_asset}} to {{%asset}} ... done (time: 0.023s)
>drop comment from table {{%asset}} ... done (time: 0.011s)
> add comment on table {{%asset}} ... done (time: 0.009s)
>drop column plan_id from table {{%asset}} ... done (time: 0.064s)
*** Applied M191016_070338_RENAME_PLAN_ASSET_TO_ASSET (Time: 0.120s)

1 migration was applied.

migrated up successfully.