0
I'm building a data model for an application. I'm relatively new to data modeling. I feel like there should be a textbook answer for the problem I'm struggling with, but don't know what the right question to ask is. Could someone help me out?
I've chosen to keep the physical database design flexible so I've chosen to include multiple types in one table (for now) rather than build separate tables for each type.
For Example (My Logical Model):
I have a table named: Animal
which holds the types Cat
Dog
and Bear
. All three types have a height
but a Cat
has a MeowVolume
the Dog
has a BarkFrequency
and the Bear
has a ClawLength
.
So the physical design would look like this:
TableName: Animal
id | height | meow_volume | bark_frequency | claw_length | animal_type_id
TableName: AnimalType
id | description
The AnimalType table would include 3 entries for Cat
Dog
and Bear
Here's where I'm struggling:
I don't have an easy way to keep track of which columns belong to which types. If someone wants to see which columns belong to which types, they would have to open the code to see the queries that construct the types. As the number of columns and types increases, I fear viewing the database may become more and more ambiguous as to which columns belong to which types.
One could argue that if the columns are not null then they belong to the type defined in the row, and that if they are null then they don't belong. But what happens if the columns are optional for the type? Then I get nulls when the column does belong to the type.
I've considered naming the columns like this: dog_bark_frequency
cat_claw_length
but then when the column is shared by multiple types the naming get unwieldy E.g. cat_dog_bear_height
and would require renaming with the addition of each new type.
Can anyone help me define the problem I'm having or offer advice on how to build it better?
The problems with your table are the result of the decision to to keep the physical design flexible. Basically you can't have both (flexibility and well-structured data). Without knowing more about the application on top of the database it's difficult to propose other approaches (besides normalizing your data model which often is the best approach). – nohillside – 2020-01-24T21:52:37.523