[Changing subject line as this strays away from the original topic]
On Mon, 2014-10-27 at 08:22 +0000, Simon Slavin wrote:
> On 27 Oct 2014, at 8:17am, Clemens Ladisch <clem...@ladisch.de> wrote:
>
> > Tristan Van Berkom wrote:
> >> locateFKeyIndex() function issuing the not-so-informative
> >> message "foreign key mismatch" [...]
> >>
> >> o When foreign keys are enabled at CREATE TABLE time, it would
> >> be very helpful at this point to issue an error if a foreign
> >> key is declared which refers to a non-unique column (or compound
> >> FK referring to a non-unique set of keys in the parent table).
> >
> > The referenced table might not yet exist.
>
> This is the problem, and it occurs with both FOREIGN KEYs and TRIGGERs.
>
> I could do my standard thing and say that a schema with circular references
> between tables is badly designed but I don't actually know whether this is
> true or not. Nevertheless, SQL allows circular references so SQLite should
> deal with them.
As I replied before, I had been using an old version of SQLite, but I
was curious of what your opinion would be on this, as I think the
compound foreign key referring to other rows in the same table is
an elegant solution in my particular use case.
The data itself is converted to json for consumers of the backend
API (and we don't want a NoSQL solution for this because we really
need a relational database for the queries we use).
The 'event' record in question looks a bit like this:
{
"uid" : 1,
"name" : "event name",
...
"participants" : [
{
"id" : 1,
"type" : "equipment",
"function" : 100, /* The type of equipment reference */
"resource" : 100, /* The reference to the actual equipment item */
"responsible" : 2 /* Refers to the employee below */
},
{
"id" : 2,
"type" : "employee",
"function" : 200, /* The role the employee will play */
"resource" : 200 /* The reference to the actual employee */
}
]
}
In this case, we need the "responsible" reference to refer to another
employee participant in the same event, we dont want to refer to the
actual resource as the designated responsible employee but rather
the "participant slot" on the given event (this simplifies the logic
as a resource may be replaced on the event but will consequentially
still be responsible for the same equipment).
So in this case, we have a table (as in the example I posted), which
is 'event_participant' and it's compound primary key is basically the
event UID along with the participant ID. So participant IDs are only
unique per event UID, and the compound foreign key reference for the
responsible_id refers to another row in the same table.
This seems to me to be an elegant solution, but I'd be curious to
know if it would be considered badly designed for some reason, and
would be interested to know what kind of alternatives people would
propose.
Anyway, no need to discuss this at length, I *am* satisfied with
the approach we've taken, only asking this out of curiosity :)
Cheers,
-Tristan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
