Re^3: key value in text format

I think ideally you will model your (composite) key as separate columns. Then you can query either all key columns or just a subset of them.

If you want to keep things simple, keep the optional key-value pairs at the end as a single string. If you want to also query them, an approach a slight step better is to format and store them as JSON. Then you can query them in the database almost as if they were additional columns. The ideal way is to convert these optional things either into a fixed set of additional columns or add another table that consists of three columns, (row-key, keyname, value). But doing that makes the queries somewhat more ugly.

Comment on Re^3: key value in text format

Replies are listed 'Best First'.
Re^4: key value in text format by Don Coyote (Hermit) on Nov 06, 2019 at 10:32 UTC
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.	[reply]

Replies are listed 'Best First'.

Re^4: key value in text format
by Don Coyote (Hermit) on Nov 06, 2019 at 10:32 UTC

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.

[reply]