Hi Daniel,
Derby only supports value semantics, not pointer or reference semantics.
In the situation you describe, you will get two intact copies of the blob.
Another solution might be a schema like this:
BinariesHistory( id, versionNumber, blob, userID, creationTimestamp,
primary key( id, versionNumber ) )
Binaries( id primary key, newestVersionNumber, foreign key( id,
newestVersionNumber ) references BinariesHistory( id, versionNumber ) )
Hope this helps,
-Rick
Daniel Noll wrote:
Hi all.
This is probably a weird question. I was considering implementing a
database something like this...
CREATE TABLE Binaries {
id INTEGER NOT NULL PRIMARY KEY,
data BLOB(1G) NOT NULL
}
CREATE TABLE BinariesHistory {
id INTEGER NOT NULL,
data BLOB(1G) NOT NULL,
version INTEGER NOT NULL,
user_id INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL,
PRIMARY KEY (integer, version)
}
In this fashion querying Binaries for an item will be quicker than
doing it for BinariesHistory as the table size will be fairly large
and Binaries has only a single id to look stuff up whereas with
BinariesHistory you would need to return all items and select the one
where version = MAX(version).
My concern is not knowing what happens to the BLOB. If I insert into
BinariesHistory and then do a second query which copies part of the
row I just inserted into Binaries, will Derby create a second copy of
the BLOB (undesirable) or will it create a reference to the *same* BLOB?
Daniel
