Re: [IndexedDB] Compound and multiple keys

FWIW, I share your concern that A is kind of forcing a schema upon users in
a bad way.  But I think all the other arguments point towards A.  I really
don't like the idea of having duplicate data.  And if we go with B, then if
I want to index on both firstname, lastname and lastname, firstname then
I'll have to have duplicate information.  This gets worse when your compound
indexes get more complicated.

So, after thinking about this for a bit, I think we should just go with A.

Also, I don't think we should allow primary keys to be compound.  I can't
think of much precedent in other databases and it'd make things considerably
more complicated for no great benefit.

J


On Thu, Jan 20, 2011 at 11:07 AM, Keean Schupke <keean@fry-it.com> wrote:

> Out of line keys (B) for me. You can have a key that is not an object
> property that way... and you can include the key in the object optionally.
> There is also no need to give the key fields in advance. These two things
> together make this the best option IMHO.
>
> Keean
>  On 20 Jan 2011 10:52, "Jeremy Orlow" <jorlow@chromium.org> wrote:
> > Ok. So what's the resolution? Let's bug it!
> >
> > On Fri, Dec 10, 2010 at 12:34 PM, Jeremy Orlow <jorlow@chromium.org>
> wrote:
> >
> >> Any other thoughts on this issue?
> >>
> >>
> >> On Thu, Dec 2, 2010 at 7:19 AM, Keean Schupke <keean@fry-it.com> wrote:
> >>
> >>> I think I prefer A. Declaring the keys in advance is stating to sound a
> >>> little like a schema, and when you go down that route you end up at SQL
> >>> schemas (which is a good thing in my opinion). I understand however
> that
> >>> some people are not so comfortable with the idea of a schema, and these
> >>> people seem to be the kind of people that like IndexedDB. So, although
> I
> >>> prefer A for me, I would have to say B for IndexedDB.
> >>>
> >>> So in conclusion: I think "B" is the better choice for IndexedDB, as it
> is
> >>> more consistent with the design of IDB.
> >>>
> >>> As for the cons of "B", sorting an array is just like sorting a string,
> >>> and it already supports string types.
> >>>
> >>> Surely there is also option "C":
> >>>
> >>> store.add({firstName: "Benny", lastName: "Zysk", age: 28},
> ["firstName",
> >>> "lastName"]);
> >>> store.add({firstName: "Benny", lastName: "Andersson", age:
> >>> 63}, ["firstName", "lastName"]);
> >>>
> >>> Like "A", but listing the properties to include in the composite index
> >>> with each add, therefore avoiding the "schema"...
> >>>
> >>>
> >>> As for layering the Relational API over the top, It doesn't make any
> >>> difference, but I would prefer whichever has the best performance.
> >>>
> >>>
> >>> Cheers,
> >>> Keean.
> >>>
> >>>
> >>> On 2 December 2010 00:57, Jonas Sicking <jonas@sicking.cc> wrote:
> >>>
> >>>> Hi IndexedDB fans (yay!!),
> >>>>
> >>>> Problem description:
> >>>>
> >>>> One of the current shortcomings of IndexedDB is that it doesn't
> >>>> support compound indexes. I.e. indexing on more than one value. For
> >>>> example it's impossible to index on, and therefor efficiently search
> >>>> for, firstname and lastname in an objectStore which stores people. Or
> >>>> index on to-address and date sent in an objectStore holding emails.
> >>>>
> >>>> The way this is traditionally done is that multiple values are used as
> >>>> key for each individual entry in an index or objectStore. For example
> >>>> the CREATE INDEX statement in SQL can list multiple columns, and
> >>>> CREATE TABLE statment can list several columns as PRIMARY KEY.
> >>>>
> >>>> There have been a couple of suggestions how to do this in IndexedDB
> >>>>
> >>>> Option A)
> >>>> When specifying a key path in createObjectStore and createIndex, allow
> >>>> an array of key-paths to be specified. Such as
> >>>>
> >>>> store = db.createObjectStore("mystore", ["firstName", "lastName"]);
> >>>> store.add({firstName: "Benny", lastName: "Zysk", age: 28});
> >>>> store.add({firstName: "Benny", lastName: "Andersson", age: 63});
> >>>> store.add({firstName: "Charlie", lastName: "Brown", age: 8});
> >>>>
> >>>> The records are stored in the following order
> >>>> "Benny", "Andersson"
> >>>> "Benny", "Zysk"
> >>>> "Charlie", "Brown"
> >>>>
> >>>> Similarly, createIndex accepts the same syntax:
> >>>> store.createIndex("myindex", ["lastName", "age"]);
> >>>>
> >>>> Option B)
> >>>> Allowing arrays as an additional data type for keys.
> >>>> store = db.createObjectStore("mystore", "fullName");
> >>>> store.add({fullName: ["Benny", "Zysk"], age: 28});
> >>>> store.add({fullName: ["Benny", "Andersson"], age: 63});
> >>>> store.add({fullName: ["Charlie", "Brown"], age: 8});
> >>>>
> >>>> Also allows out-of-line keys using:
> >>>> store = db.createObjectStore("mystore");
> >>>> store.add({age: 28}, ["Benny", "Zysk"]);
> >>>> store.add({age: 63}, ["Benny", "Andersson"]);
> >>>> store.add({age: 8}, ["Charlie", "Brown"]);
> >>>>
> >>>> (the sort order here is the same as in option A).
> >>>>
> >>>> Similarly, if an index pointed used a keyPath which points to an
> >>>> array, this would create an entry in the index which used a compound
> >>>> key consisting of the values in the array.
> >>>>
> >>>> There are of course advantages and disadvantages with both options.
> >>>>
> >>>> Option A advantages:
> >>>> * Ensures that at objectStore/index creation time the number of keys
> >>>> are known. This allows the implementation to create and optimize the
> >>>> index using this information. This is especially useful in situations
> >>>> when the indexedDB implementation is backed by a SQL database which
> >>>> uses columns as a way to represent multiple keys.
> >>>> * Easy to use when key values appear as separate properties on the
> >>>> stored object.
> >>>> * Obvious how to sort entries.
> >>>>
> >>>> Option A disadvantages:
> >>>> * Doesn't allow compound out-of-line keys.
> >>>> * Requires multiple properties to be added to stored objects if the
> >>>> components of the key isn't available there (for example if it's
> >>>> out-of-line or stored in an array).
> >>>>
> >>>> Option B advantages:
> >>>> * Allows compound out-of-line keys.
> >>>> * Easy to use when the key values are handled as an array by other
> >>>> code. Both when using in-line and out-of-line keys.
> >>>> * Maximum flexibility since you can combine single-value keys and
> >>>> compound keys in one objectStore, as well as arrays of different
> >>>> length (we couldn't come up with use cases for this though).
> >>>>
> >>>> Option B disadvantages:
> >>>> * Requires defining sorting between single values and arrays, as well
> >>>> as between arrays of different length.
> >>>> * Requires a single property to be added to stored objects if the key
> >>>> isn't available there (for example if it's stored as separate
> >>>> properties).
> >>>>
> >>>> There is of course a third alternative: Do both Option A and Option B.
> >>>> This brings most of the advantages of both options, but also many of
> >>>> the disadvantages of both. It also adds a lot of API surface which
> >>>> could conflict with future features, so it's something I'd really like
> >>>> to avoid.
> >>>>
> >>>>
> >>>> Questions:
> >>>>
> >>>> The main question we had if there is a use case for having different
> >>>> number of compound-key-values for the entries in a index or
> >>>> objectStore? I.e. is there a case when you, in one objectStore, want
> >>>> to have one record with a compound key consisting of 2 different
> >>>> values, and another record consisting of 3, 4 or 5?
> >>>>
> >>>> In all the cases where I've used compound keys, each key-part has been
> >>>> vital. For example a table storing sale totals by quarter, country and
> >>>> price class. In this case it's obviously always going to be 3 parts to
> >>>> the compound key. Does anyone have counter examples?
> >>>>
> >>>> Similarly, are there use cases which require compound keys that
> >>>> doesn't have a hard limit on the number of values? I.e. where you
> >>>> could find out more and more detail about an item and describe that by
> >>>> adding additional values to the key.
> >>>>
> >>>> Another question is if there are databases out there which allow using
> >>>> arrays as keys, similar to option B above. It seems particularly
> >>>> likely to find "NoSQL" databases that uses this. None of the SQL
> >>>> databases we looked at allowed keying off of arrays, which isn't
> >>>> terribly surprising since SQL databases tend to create compound keys
> >>>> using separate columns, rather than multiple values in a single
> >>>> column.
> >>>>
> >>>>
> >>>> Suggested solutions:
> >>>>
> >>>> I'm currently leaning towards option A above. However I'd love to get
> >>>> input from people with more database experience than me (especially
> >>>> since mine is very SQL based), before that I don't have strong
> >>>> opinions either way.
> >>>>
> >>>> / Jonas
> >>>>
> >>>>
> >>>
> >>
>