How to relate columns in a table to a specific type?

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?

teaMonkeyFruit

Posted 2020-01-24T19:24:20.920

Reputation: 11

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

No answers