Hi Jeremy, Thanks for your reply and suggestions. As expected, "there's Sequel feature for that" somewhere in the code / documentation. ;-) I will try out the various versions over the holiday, time permitting.
The basic idea behind using a single audit_log table is the simplicity of searching for all actions by a user. If a separate table is added for each audited model that becomes very cumbersome, but perhaps Sequel already have a feature for that too (??). As for using a string PK. that is sometimes the most appropriate way. I'm currently working on a project (in my "spare" time) where "A-01-01" is the most natural logical PK for a key entity. This pk is repeated in around 20+ other tables that in some way relates to the core entity. By using the real-life human-friendly natural string PK, the data in each sub-table stays unambiguous, ie: it's easy to know which actual entity the row belongs to. (the referred to pk is fixed and will not change) I will probably convert it all to Postgres & JSON only for a schemaless version, but that requires further studies and will probably encounter some other issues. Anyway, thanks for your time and attention. Regards, Kematzy On Monday, 21 December 2015 13:15:35 UTC+8, Jeremy Evans wrote: > > On Sunday, December 20, 2015 at 3:24:00 PM UTC-8, Kematzy wrote: >> >> >> Hi Jeremy, >> >> Thanks for your reply. >> >> Actually, I was too rushed yesterday morning to fully understand the >> problem and therefore blamed the wrong thing. My bad & my sincere >> apologies. >> >> I now believe the problem seems to occur when collecting records via a >> *one_to_many* association. >> >> You can see the entire codebase here [ >> https://github.com/kematzy/sequel-audited/tree/postgres-errors-example ] >> with the results fully outlined here [ >> https://github.com/kematzy/sequel-audited/blob/postgres-errors-example/Error-Output.md >> >> ]. >> >> I thought it was better to see the problem within the context rather than >> isolated, but if this is not suitable, please let me know and I will try to >> compress the code to minimum required. >> > > The problem here is having a string foreign key referencing a integer > primary key. PostgreSQL doesn't have implicit casts of integer to string, > so it doesn't like that. If the primary key is an integer, the foreign key > should be an integer, and vice-versa. > > There are probably ways to work around the problem in Sequel using > association options that will do the necessary casts ( > http://sequel.jeremyevans.net/rdoc/files/doc/association_basics_rdoc.html#label-Associations+Based+on+SQL+Expressions+Options), > > but my recommendation would be better to fix the design. I see three > possible approaches: > > 1) If you must have a single audit table that can handle both string > primary keys and integer primary keys, you could have two foreign key > columns, one for a string primary key and one for an integer primary key. > This would have issues if the main table has a composite primary key, or a > non-string/integer primary key. > 2) You could switch to a more relational approach of a separate audit > table per main table, with a proper foreign key > 3) You could go the schemaless route and store the reference information > in a json column > > Thanks, > Jeremy > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
