On 4 Oct 2011, at 8:30am, Ivan Shmakov wrote:
> This structure is, obviously, could just as well be represented
> with, e. g.:
>
> CREATE TABLE "foo" (
> "key" INTEGER PRIMARY KEY,
> "value" INTEGER NOT NULL,
> "value-1" INTEGER,
> …
> "value-N" INTEGER);
>
> (I. e., by abandoning the NOT NULL constraint from the
> respective columns.) For which I could then specify an
> uniqueness constraint:
>
> CREATE UNIQUE INDEX "foo-unique"
> ON "foo" ("value", "value-1", …, "value-N");
Do you actually mean to CREATE one UNIQUE INDEX that checks that the
/combination/ of values is unique, or did you mean to CREATE a UNIQUE INDEX on
each value ? There's nothing to stop you going
CREATE UNIQUE INDEX foo-1 ON foo (value-1);
CREATE UNIQUE INDEX foo-2 ON foo (value-2);
CREATE UNIQUE INDEX foo-3 ON foo (value-3);
and it will take up no more space than the individual indices on separate
tables.
> Now, I'm somewhat concerned that the table above may get overly
> sparse at times, which makes me wonder if I could establish an
> equivalent constraint over the original multi-relation
> structure, as described above?
Storing NUL values is not space-consuming for SQLite. SQLite has a special
coding for NUL which means "This is a NUL don't even bother going to look for
it.". Similar things are done for the integer values 0 and 1, often stored
because they correspond to FALSE and TRUE.
Apart from that, the only consideration would seem to depend on what proportion
of all these values were non-NULL. If only a tiny proportion of fields had
values I might use a sparse system. If perhaps a tenth of values were filled
in I might use the grid system. But there's a half-way sparse alternative you
might not have come up with:
CREATE TABLE fooValues (
key INTEGER PRIMARY KEY
REFERENCES "foo" (key),
column INTEGER,
value INTEGER NOT NULL);
CREATE UNIQUE INDEX fooValuesKeyColumn ON fooValues (key, column);
This is a classic 'property list' or 'triplet' setup suitable for sparse
information. Is that what you were asking for ? It enforces structure
correctly with just one table and one explicitly defined index, which is quite
efficient.
Any of the subtable forms tend to involve you using INSERT OR REPLACE a lot.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
