Foreign Key Manual Optimization
If database designer knows some information about data that will be stored in Tables, he can do some optimisations to reduce disk space used by Primary Key and Foreign Keys.
Let you know that some Table A can have equal or less than:
255 records = 1 byte 65,536 records = 2 bytes 16,777,216 records = 3 bytes
With this information you can specify a corresponded column type:
- for Primary Key and therefore
- for Foreign Keys that point it.
Let you have Table Students. University can have few thousands of students. So we can choose Student_ID to be USHORT type (2 bytes). If we have 5000 students we have win 10Kb of disk space. Also at least 10-15Kb we win in index of this PK.
Hmm, looks not many? But lets look now on Foreign Key. We can note that around of Student table can be several tables that have relation 1 : M to Student. Each of this table have FK pointing Student table. Each of this table most probably have much more records than Student table. So we can easy assume that we have e.g. 100,000 records around of Students. Then our win easy jump to
2 bytes * 100000 = 200Kb for Tables about 200-300Kb for indexes of FK fields.
Note, we get this advantage without any penalty. We are able to get this advantage only because we have some special knowledge about data on the stage of database design.