As long as there were no security concerns, another table could store sparse records from a number of the databases.
Providing a primary key of the filename, along with the three columns Corion already suggested. The records db would then hold a sparse column with a boolean value denoting the record is held on that db. Checking the boolean value and retrieving from the other table when requiring the sparse values.
Another approach that has just occured to me, of the ugly variety, with additional overhead, may be to hash the filenames before entering them onto the database. The first row primary key being the filename itself hashed, with the secondary row being the filename concatenated with for example, the term 'sparse' before being hashed. Already I can feel the glares.
The issues with this would be losing the key information, you would necessarily need to know beforehand the key values of sparse data. Using an additional table to denote the keys could be a solution. At this stage it would then be a matter of performance requirements, size of records and whether there is any advantage of having relatively few empty record rows with additional tables denoting keys for each db, along with the requirement to hash/dehash, versus additional mostly empty column(s).
Likely the better solution is to serialise in such a format as JSON to keep the db contained. But at this point would you not just store the whole optional hash record as a JSON blob anyway, meaning there would only need be the one additional column.
On reflection, this is a partial conversion of the binary data, but for this kind of mixed data there is more than one step needed.
In reply to Re^4: key value in text format
by Don Coyote
in thread key value in text format
by pwagyi
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |