I am trying to structure my data base for an initial build in an application and I have some confusion with the data types.
Data Type Male:
Name
Color
Description
Data Type Female:
Name
Color
Description
The confusion stems with the color fields. Both male and female have colors of which I would like to setup a list of default color options. The question is, should Color be its own separate data type with fields of type text for each color? Or should I build the colors under both Male and Female in each data type.
Color is one example of this in my data structure but it is the best example of the confusion I am having.
Also, I am confused if each should be a list of colors or a single field type of color? Essentially I would like a list of default colors to select in a multi-dropdown menu but also have the ability for the user to input their own colors if none of the default fit the description.
Is this possible? Where does one list the colors available and under what type of data structure is this?
I definitely wouldn’t use texts - An Option set would most likely be the best option but, depending on the details of your app, a separate datatype might also be a good idea.
Also, I am confused if each should be a list of colors or a single field type of color?
Well that obviously just depends on whether you want it to be a list or not (i.e. a User can have more than 1 colour)
Is this possible?
Yes, that’s possible… it’s a bit fiddly but not too difficult (there are multiple posts about that in the forum)… you won’t be able to use an Option Set for that though, so your colours will need to be a Datatype…
Just one last point to make… is there a specific reason for having a separate datatype for Male and Female? - Seeing as they have the same fields would it not be more efficient to combine them into a single datatype, and just have a field to distinguish male from female?
Thanks for your reply Adam, honestly this part is the struggle that I am trying to understand.
For instance, in my app I will have different species of animals, in the beginning I will start with one or two but there are many.
Each species has a male and female.
Each male and female has different colors.
Any idea how I can approach the database where I can build the app based on users adding data to male and females while also having the ability to add more species while the app expands?
I’d have a datatype for “Species” (or Animal)
I’d use an Option Set for “Sex” (with 2 options: Male and Female)
And (as you want users to be able to add new colours), I’d have a Datatype for Colour
Obviously, the Animal (or Species) datatype would have fields for “Sex” (of type Sex) and “Colour” (of type colour), along with whatever other necessary data you need for each animal.
Adam thank you again for your help, this really clears it up. I had another question though. As different species have different colors, but many of the colors are the same.
What would be the best way to structure the color in the database?
Also, I could manually input the species data one species at a time of which the user could then select, would this be effective at adding each species and the colors for the species?
From a user experience perspective, I don’t want 100 colors for each species, I would only like the colors for the species selected to be available for the user, what would you recommend for a setup in the database to do this?
When thinking of this in more detail, each type of animal would have a species, name, sex and color.
I almost think it is better to make species, sex, and color option sets since this would be data that the user doesn’t need to input or make changes too. Only select an option for.
Then I should make a data type for the animal with attributes for name (so the user can input different names for each animal), species (of type species), color (of type color) and sex (of type sex). I would also like them to be able to upload a picture of each animal individually and select which pen the animal would live in. Meaning I would also need a datatype where a user can add different pens but under animal it would also be a pen (of type pen).
Do you see any flaws in this type of database structure? Each user shouldn’t have more than 200 animals, so I don’t think this would create issues with scaling.
