On Fri, 17 Jun 2016 19:59:56 +0200 Rapin Patrick <rapin.patr...@gmail.com> wrote:
> 2016-06-17 18:24 GMT+02:00 James K. Lowden <jklow...@schemamania.org>: > > > You are encoding type information in the name. If you move the type > > information into the data, SQLite can manage the unit dimension. > > You could use a CHECK constraint to require that speed was in m/s, > > or a trigger to make it so. > > That would indeed be an option. I suppose that you mean to use a > string like "2.34 m/s" or a BLOB of typically 9 bytes (a 'double' and > a unit enum). My first instinct is to add a Units column to any table with a measurement. The value in Units would be a foreign key to a Units table. Another table, Conversions, could hold conversion factors. That would support multiple units for a given column in a table or, with a trigger, enforce (and coerce) all values in a column to use a single unit. It would also permit arbitrary conversion in SELECT, simply by reference to the Conversions. A more sophisticated DBMS would have stored procedures for inserts, user-defined types, and INFOMATION_SCHEMA. With those, you could define a type domain for each measurement. You could forgo the Units column, and instead use just a Units parameter. The stored procedure would look up the units for the measurement's domain, and apply the any conversion or reject the insert. That seems be roughly what you're doing with your C++ wrapper. > But this would slightly increase the database size, and slow down > access due to formatting / parsing. I doubt you would notice. > In addition, my approach had the big advantage to being backward > compatible: older versions of our application would just ignore the > column unit type, and read/write regular 'double'. If I were working with you, I would object to the requirement that the column names encode anything. I would want the units expressed explicitly in the database, however they might be applied or enforced. >From an application-programming perspective, I would prefer to treat the column names as opaque identifiers. Rather than parsing them for "x_per_y", I'd prefer to look up the units by column name. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
