Comment by m33k44 | Hacker News Reader

Comment by m33k44 | Hacker News Reader

Parent Full thread

  > 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.

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.

What is the reason for avoiding an extra field considering the alternatives mentioned throughout this post?

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.

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.

Reply on news.ycombinator.com