I have a table set up with many columns (up to 15) and they are made to hold the value of what each column is supposed to hold, making all but two be able to intake NULL values.
Here is an example of what I would have:
| Name | Age | Favorite color | Favorite food | Have pet? | Lucky number |
|---|---|---|---|---|---|
| Alison | 22 | Purple | False | 10 | |
| Bob | 34 | Tuna Sandwich | False |
The way I am trying to insert the data is through two steps:
- Asking which ones they are going to answer
- Receiving the answers one by one
Because this is through two steps, I am not sure whether this would have to include two tables where one would be to list which questions they would answer, the other being to hold boolean values of whether they are going to be answering those questions.
What I have tried so far was to just input their name in those certain columns of the questions they say they will answer and then later edit the value while receiving the answers, but I realized it is rather difficult especially because not all columns are for string types.
Just to picture it, this is what I have tried:
| Name | Age | Favorite color | Favorite food | Have pet? | Lucky number |
|---|---|---|---|---|---|
| Alison | 22 | Alison | Alison | Alison | |
| Bob | 34 | Bob | Bob |
Name: String
Age: Int
Favorite color: String
Favorite food: String
Have pet?: Boolean
Lucky number: Int
This won't work since "Alison" and "Bob" aren't integer or boolean for the "Have pet?" or "Lucky number" columns.
Would this have to be made with two separate tables? Or is there any way I can do this without having to create a second one?
CodePudding user response：
You can set it up so all values are nullable and then the columns that have values filled in you know that they answered. Maybe Name, and age are required so you can leave those as non-null
| Col | Type |
|---|---|
| name | string |
| age | int |
| favoriteColor | string | null |
| favoriteFood | string|null |
| havePet | boolean|null |
| luckyNumber | integer|null |
CodePudding user response：
When you have dynamic attributes like this, it's generally better to use an attribute-value table instead of separate columns for each attribute. So you have a table like:
| Name | Attribute | Value |
|---|---|---|
| Alison | age | 22 |
| Alison | color | purple |
| Bob | food | tuna |
| Bob | pet | true |
| Bob | age | 34 |
Then you can have another table that describes the properties of each attribute:
| Attribute | Datatype |
|---|---|
| age | int |
| food | string |
| color | string |
| pet | boolean |
With this structure you can easily add new attributes without having to change any table schemas. You can see a well known example of this in the wp_postmeta table used by WordPress.
CodePudding user response：
How about adding another column to your table called questionsToBeAnswered?
Then you could fill that in with a comma separated string of the column names, or you could make that a json column and use an array.