What is the best practice for storing a discriminator column ? Does it make any difference ?
Any pros/cons of using one over the other ?
I am using MySql with Entity Framework but the question goes beyond the underlying technology i think.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Use always the smallest data types that fits the values it will hold. 'Fit' is a flexible term, depending on thing like:
Once you have those under consideration, use the smallest data type, as it will reduce memory and disk footprint (potentially reducing IOPS).
An int will take 4 bytes always, and it can store values from -2147483648 to 2147483647 (or from 0 to 4294967295, if using unsigned).
A varchar will take
1 + char_encoding * length (or
2 + char_encoding * length if it is larger than 255). char_encoding can be between 1 and 4 bytes, depending on the character and the encoding. Also, that assumes dynamic storage, fixed row engines like memory will store it with the maximum theoretical size. Usually inefficient for numeric values.
An enum will take 1 byte (or 2, if it holds more than 255 values), but will not allow for values not defined at creation time without an
So it is always a trade-off between flexibility and performance.
Inheritance serialization, however, deserves its own discussion.