5 ms·

Are there any significant differences between AnimalCat and AnimalDog? Feels like there can just be an "animals" table with an enum "type" field or a one to man

Are there any significant differences between AnimalCat and AnimalDog? Feels like there can just be an "animals" table with an enum "type" field or a one to many animaltype(id, type). Normally with such hierarchies it doesn't neccesary need to represent the same structure unless there is a significant difference as we can filter by animaltype with a where clause. The same approach cannot be applied to rmdbs design as to oo because the paradigms are different.

m33k44 7y ago
> Are there any significant differences between AnimalCat and AnimalDog? Feels like there can just be an "animals" table with an enum "type" field The AnimalCat and AnimalDog are just relationship tables. The alternative to not using separate relationship tables is, just as you said, to use an enum type field in the Animal table. I was actually trying to avoid using an extra field in the table to indicate type, hence my question on what are better approaches to avoid both table size increase and performance issues.
- dillonmckay 7y ago
  An enumerated field will be represented most likely as an integer value in the row, so keep that in mind. Otherwise, you are going to use a foreign key lookup to get your type, which will also be an integer stored in that same row. But, if you wanted to add a new animal type, and you used a lookup table, even if you have billion of rows, you are not messing with the table with a bunch of data. That may not be the case with an enum. It sucks to ALTER a live table in production with billions of rows being actively INSERTed.
- deleted 7y ago
  [deleted]
- paktek123 7y ago
  What is the reason for avoiding an extra field considering the alternatives mentioned throughout this post?
  - m33k44 7y ago
    Just one 64 bit size field in a 1 billion row table will amount to about 8 GB data for that table. Edit: Basically what I am looking for is a trade-off between space and performance.
    - paktek123 7y ago
      8gb seems quite small in the grand scheme of things. Storage is fairly cheap nowadays. I'm sure there will be other concerns with scaling when you do hit the 1 billion mark.

m33k44 7y ago

> Are there any significant differences between AnimalCat and AnimalDog? Feels like there can just be an "animals" table with an enum "type" field The AnimalCat and AnimalDog are just relationship tables. The alternative to not using separate relationship tables is, just as you said, to use an enum type field in the Animal table. I was actually trying to avoid using an extra field in the table to indicate type, hence my question on what are better approaches to avoid both table size increase and performance issues.

dillonmckay 7y ago
An enumerated field will be represented most likely as an integer value in the row, so keep that in mind. Otherwise, you are going to use a foreign key lookup to get your type, which will also be an integer stored in that same row. But, if you wanted to add a new animal type, and you used a lookup table, even if you have billion of rows, you are not messing with the table with a bunch of data. That may not be the case with an enum. It sucks to ALTER a live table in production with billions of rows being actively INSERTed.
deleted 7y ago
[deleted]
paktek123 7y ago
What is the reason for avoiding an extra field considering the alternatives mentioned throughout this post?
- m33k44 7y ago
  Just one 64 bit size field in a 1 billion row table will amount to about 8 GB data for that table. Edit: Basically what I am looking for is a trade-off between space and performance.
  - paktek123 7y ago
    8gb seems quite small in the grand scheme of things. Storage is fairly cheap nowadays. I'm sure there will be other concerns with scaling when you do hit the 1 billion mark.