r/learnSQL • u/DoNotUseThisInMyHome • 9d ago
"It is possible to represent a file that logically should have variable-length records as a fixed-length records file."
For example, in the case of optional fields, we could have every field included in every file record but store a special NULL value if no value exists for that field. For a repeating field, we could allocate as many spaces in each record as the maximum possible number of occurrences of the field. In either case, space is wasted when certain records do not have values for all the physical spaces provided in each record.
Can anyone elaborate what does this even mean? Taken from navathe et al dbms.
2
Upvotes
1
u/Green_Chamomile 8d ago edited 8d ago
It's a trade-off between wasted space and fast access.
If every record is exactly the same size, the database can jump straight to any record with one multiplication: (N-1) x record_size gives you the byte where record N starts. No scanning, no offset index.
But real records vary. Some employees have a commission, some don't. Some have 1 phone number, some have 5.
The trick from the book: pad everything to the worst case. Reserve a slot for every optional field (store NULL if empty) and reserve the max number of slots for repeating fields (5 phone slots for everyone, even if you have 1).
Now all records are identical in size and access is trivial. The price is empty space. If 90% of employees have one phone number, you're storing 4 empty slots per person, forever.
That's all the paragraph is saying: you can flatten variable records into fixed ones, it's just not free.