Question 21004 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I set a mySQL table to know what will eventually be filled?

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?

Tags:

3

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search