For storage purposes I would use normal column types, ints, whatever. For duplicate checking, I would in the app, calculate a unique hash from all the necessary fields taking part in the duplicate check. That hash goes into a varchar column that has a unique constraint. The db engine then enforces uniqueness..



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 06 March 2015 03:59 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Newbie question on how to hold an unusual data type





Hi all,

I have volunteered to write a statistics-type program in
Lazarus/FreePascal under Linux and I need an embedded database, that
means it has to be Firebird. I've used a lot of database software
before, but never Firebird (nor Interbase, for that matter). I do NOT
want to force users to install a full-blown database server in order
to use the software.

My problem is that I must avoid duplicated records in the database,
the unique key is a complicated structure containing four 16-bit words
plus a 108-bit set of flags. The combination of the whole lot must be
unique. I don't need to retrieve this data other than to check for
duplicate records, so I can massage it in FreePascal so that it can go
into the database in any form that's desirable.

If I were using PostgreSQL, I'd store the whole lot as a single
172-bit bitstring, but I can't find any mention of an equivalent data
type in the Firebird documentation that I've been able to find.

So, how would you store this data for greatest speed/efficiency in
checking for duplicates, please? I'm looking at a few million records
in the database, and there's a record size of around 350-400 bytes.

Thanks,

Brian.





[Non-text portions of this message have been removed]