D
D
Dnebl2016-12-04 01:25:33
MySQL
Dnebl, 2016-12-04 01:25:33

What is the best way to structure tables?

There is a table with applications, the application has a status (Accepted or Rejected). If the status is Denied, there should be a comment with the reason for the refusal. What is the best way to organize the table structure?
The following options came to mind:
1. In the application table, in the status field, store json of the form

{
'status' : "...", 
'comment' : "..."
}

2.Create a status table like this
table 1
id , 
status_name.

Create an application status
table table 2
id, 
status_id, 
comment

In the request table, store a pointer to the request status table (Table 2).

Answer the question

In order to leave comments, you need to log in

1 answer(s)
E
Eugene Wolf, 2016-12-04
@Wolfnsex

1. In the request table in the status field, store json of the form

You shouldn't do this in MySQL, JSON fields are not indexed.
I would do this:
Create a table of the desired structure, add the fields:
status - ENUM (list), of two items, of the accepted/rejected type. If there are not many values, a list is fine.
status_comment - comment to the status, which is filled only if the status is "denied".
*The actual presence of a field in the table does not mean that it must be filled in.
If you plan to add statuses later, you can make a link to the add. status table.
Or, statuses can be marked simply with a number, for example,
0 - refused
1 - accepted
2 - in the process of making a decision
, etc.
And store the textual description of each status as a class/structure/object constant inside your program.
PS If it is fundamentally important that for any status other than "Refused" there would be no comment to the status, it is at the database level - you can hang a trigger on the table that will clear this field.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question