14 Replies - 656 Views - Last Post: 26 June 2018 - 07:19 AM Rate Topic: -----

#1 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Polls with images table schema?

Posted 24 June 2018 - 04:22 PM

I am trying to create a poll application that may have images and may have no images at all. If it does have images, then it can have many or just one. For example, each choice may have an image or there may be only 1 image for all choices. Here's a good example of what I want to create: Opinion Stage.

Here's the table schema I'm thinking of:
Questions //Table of all questions with total votes to avoid querying all user votes
  -id
  -user_id (foreign key to users)
  -title
  -description
  -total_votes
  -date

Choices  //Table of all question choices, each will have vote_count to avoid querying all voting_history each time.
  -id
  -question_id (foreign key to questions)
  -choice
  -vote_count

voting_history //Table that shows which user voted for what.
  -id
  -choice_id (foreign key to choices)
  -user_id (foreign key to users)
  -date

Images  //Table that gives the images for the choices
  -id
  -choice_id (foreign key to choices)
  -img


Let's say we have only 1 image, then the first choice will have an image, but the rest of the images will not have any rows in the image table. If we have 2 choices and there are 2 images, then each choice will have an image. Is this a good way to build a table schema like this? Is it efficient?

Is This A Good Question/Topic? 0
  • +

Replies To: Polls with images table schema?

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 25 June 2018 - 04:53 AM

Don't use columns for calculated fields,

Quote

Questions //Table of all questions with total votes to avoid querying all user votes

It REALLY isn't that big a deal.

Images and Choices could be merged, unless the same choice will have multiple images or the same image will be used for multiple choices.

You may also want a field that allows determining if the poll is still active, or if a choice is no longer valid.
Was This Post Helpful? 0
  • +
  • -

#3 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Re: Polls with images table schema?

Posted 25 June 2018 - 08:18 AM

Why shouldn't I use columns for calculated fields? Also, I have updated the question on stackoverflow with no details: https://stackoverflo...022472#51022472

How can Images and Choices be merged if we might have 0, 1, 2, 3, etc. images for a poll?
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Polls with images table schema?

Posted 25 June 2018 - 08:24 AM

Wouldn't that be the job for a 'key'? Each entry has a unique key.. that unique key is tied to an image location as well.
Was This Post Helpful? 0
  • +
  • -

#5 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 25 June 2018 - 08:44 AM

What are the images tied to? The choice? The poll? The question?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Polls with images table schema?

Posted 25 June 2018 - 09:06 AM

@astone - tied to deez nutz! Ooooooooooooooooooooooooooooohhhhhhhhhhhhhhhhhhhhhhhhhh!!

Posted Image

Just kidding. That is a sound, and valid, inquiry. I have been overly stimulated with soda and kolaches this morning.
Was This Post Helpful? 1
  • +
  • -

#7 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Re: Polls with images table schema?

Posted 25 June 2018 - 09:54 AM

The images are tied to the choice. Each choice should show its correct image. For example, if the choice was Ronaldo, it should show the image of Ronaldo and if the other choice is Messi it should show Messi's image. The only time when an image is tied to no choice is when there is only 1 image for both choices. For example, let's say we have an image of the sun and the question "Do you like breakfast?" with the choices yes and no. There can also be the possibility of no images at all or sometimes 2 or more images depending on the number of choices.

So for example, if we wanted to get all images for a particular question we use question_id as foreign key to Images?

View Postrazer5665, on 25 June 2018 - 09:53 AM, said:

The images are tied to the choice. Each choice should show its correct image. For example, if the choice was Ronaldo, it should show the image of Ronaldo and if the other choice is Messi it should show Messi's image. The only time when an image is tied to no choice is when there is only 1 image for both choices. For example, let's say we have an image of the sun and the question "Do you like breakfast?" with the choices yes and no. There can also be the possibility of no images at all or sometimes 2 or more images depending on the number of choices.


View Postrazer5665, on 25 June 2018 - 09:54 AM, said:

The images are tied to the choice. Each choice should show its correct image. For example, if the choice was Ronaldo, it should show the image of Ronaldo and if the other choice is Messi it should show Messi's image. The only time when an image is tied to no choice is when there is only 1 image for both choices. For example, let's say we have an image of the sun and the question "Do you like breakfast?" with the choices yes and no. There can also be the possibility of no images at all or sometimes 2 or more images depending on the number of choices.


.
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15226
  • View blog
  • Posts: 60,944
  • Joined: 12-June 08

Re: Polls with images table schema?

Posted 25 June 2018 - 10:06 AM

Choice A or B is different than 'affirm/decline' to a question. Perhaps plan your tables accordingly.

In the end - yes.. you should have a 'choice table'.. which has an ID uniquely for that choice.. and maybe a textfield that is the path to the image. In theory a question table would be there to have a unique id for the question, type, and choices.. establishing that relationship.. etc.
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 25 June 2018 - 10:14 AM

In that case, I would have an image path column for the question as well as the choice.
Was This Post Helpful? 0
  • +
  • -

#10 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Re: Polls with images table schema?

Posted 25 June 2018 - 10:20 AM

So 2 columns in both question and choice that is the image path? Hmm, what if the question has no images, so it would be set to null in both tables. Isn't the way I am describing a more normalized approach?

View Postastonecipher, on 25 June 2018 - 10:14 AM, said:

In that case, I would have an image path column for the question as well as the choice.


So drop the Images table and just have a column for the image path in the choices table? If there are no images for the choices, wouldn't all fields be set to null and that would make it unnormalized since we are still populating that field with null?

View Postmodi123_1, on 25 June 2018 - 10:06 AM, said:

Choice A or B is different than 'affirm/decline' to a question. Perhaps plan your tables accordingly.

In the end - yes.. you should have a 'choice table'.. which has an ID uniquely for that choice.. and maybe a textfield that is the path to the image. In theory a question table would be there to have a unique id for the question, type, and choices.. establishing that relationship.. etc.

Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 25 June 2018 - 10:33 AM

It is normalized, unless you will have the same image used for multiple choices. How likely is it that you need a many to many relationship between choices and images?

The other portion of this, if a question has an image as well, would you create a new table for poll images along side the choice image table?


Null values don't count in the data repeated normalization requirement, if that is what you were thinking.
Was This Post Helpful? 0
  • +
  • -

#12 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Re: Polls with images table schema?

Posted 25 June 2018 - 04:36 PM

So if a field is null, it does not take up space in the table? Hmm, interesting I think that may be the best option to create an image column in the Choices table. If a question has 1 image, then it'll go in the first choice and the second choice image path will be left null. Is this reasonable for performance wise?
Was This Post Helpful? 0
  • +
  • -

#13 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 25 June 2018 - 04:38 PM

Yes. You just check for a null in the code.

It isn't that it doesn't take up space, but it is a valid value for a column, unless you explicitly say that it wont allow null values.
Was This Post Helpful? 0
  • +
  • -

#14 razer5665   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 24-June 18

Re: Polls with images table schema?

Posted 25 June 2018 - 05:36 PM

I know it's a valid value, but let's say we have 1 column with a million null values for instance. Would the size of that database be 0kb for example?
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2918
  • View blog
  • Posts: 11,335
  • Joined: 03-December 12

Re: Polls with images table schema?

Posted 26 June 2018 - 07:19 AM

I don't think it is something to be concerned about, but this should shed some light on it if you are really interested,

https://dba.stackexc...ed-column-value

So, it really depends on what the declared column type is, for what it does.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1