This is a somewhat theoretical question as it does not specify any technologies used and I am currently not even working on writing the code to achieve what I desire, but a good answer would greatly help me with structuring the solution I wish to achieve when I get started writing the application. With that disclaimer out of the way, below is the problem for which I need a solution.
Say I am thinking of potentially using database table which will store some data about Easter Eggs.
The columns are as follows:
| EggNum | ColorPrimary | StripeNum | StripeColors | NumStripesOfEachColor |
There can be multiple colors of stripes on each egg and multiple stripes of a given color on each egg.
This creates a problem as there is not a good way to store that kind of data in a simple relational database table as far as I understand.
I have thought already about the solution of hard-coding more columns for each possibility, such as:
| NumRedStripes | NumBlueStripes | NumGreenStripes | … etc.
The issue with that solution is that there are hundreds of possible colors in this scenario which would require adding hundreds of columns to the table. Is there a more elegant way to go about achieving what I want to achieve, or is adding a column to hold the number present of every possible stripe color the only way it can be done?
This is also not to mention that it would require updating the database structure when a new color becomes a possibility. I feel as though that may cause a problem, but I am not sure. Also, how bloated and slow would this database become assuming there are 500 columns to be added, or would that not become a problem unless there were many more than that?
Sorry if these questions are ignorant of any concepts I should be aware of. I am, as of yet, inexperienced in working with databases. Thanks for any help in advance.
Robert McElveen is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.