(Note this situation is not exactly how it's, however i chose to make this for example)
I've an entity inside a table with data that's up-to-date every 5 seconds (Kinematic Data: Speed, Heading, Lat, Lengthy, and PositionTime), along with other data that's up-to-date hardly whatsoever, when (Color, Make, OriginTime).
Now my boss wants me to partition this data into separate tables within our database (Having a Face to face Relationship), as so:
He causes it to be seem "apparent" that it ought to be by doing this, but can there be really any benefits of getting this data separated as so for placing and upgrading (For example basically put a catalog on Color or Make)?
It could seem sensible to complete vertical partitioning such as this. Or may possibly not.
If you use a MVCC based engine, every time you update a row, it generally* copies the whole row and produces a replacement using the modifications. This really is to ensure that other transactions that not begin to see the update could browse the original row if they have to.
Which means that upgrading a couple of small posts frequently in an exceedingly wide row causes the database to perform a much more creates than it must.
Although not that lots of, because usually the engine is only going to sync its transaction log, which is exactly the same size no matter how big non-up-to-date posts, as well as since the data rows are often saved in blocks in which a whole block must be written anyway, no matter the amount of it transformed.
Therefore it seems like a potentially pointless optimisation, which like every other, should be thought about due to a) Can there be a real performance problem (i.e. is ANY optimisation needed) and b) Is particular optimisation the easiest way of fixing it?
I believe the risk of a) is not likely, and b) can also be unlikely, so the likelihood of this being needed is roughly unlikely-squared.
* Some engines make the best for large posts for example large BLOBs or text posts, that are held elsewhere and never replicated if other posts within the row are up-to-date.
If the purpose of this design would be to conserve a history from the kinematic data then your design is sensible. However, there does not appear to become a type in the
CAR_KINEMATIC table which inserts that usage. If alternatively there's a 1-to-one relationship between both of these tables the division is meritless.
Your manager is appropriate. Which is not related to "partitioning", its known as normalization.
EDIT: Ok, "vertical partitioning" is really a well-known term, and normalization is a approach to vertical partitioning. However in this situation, normalization appears to become the best answer, which describes the question (Quote: "... can there be really any benefits of getting this data separated as so for placing and upgrading"). Pros and cons for normalization are very-known. The wikipedia article is a great beginning point.
And BTW, to help keep the flames of "Erwin Smout" burning: "vertical decomposition" doesn't appear to become a generally used term here. Right?