in reply to SQL Joins across Subclasses
-Nitrox
|
---|
Replies are listed 'Best First'. | |
---|---|
Re^2: SQL Joins across Subclasses
by diotalevi (Canon) on Jan 11, 2003 at 17:01 UTC | |
If you are using PostgreSQL then there's an explicit syntax to enable this sort of thing. PostgreSQL will then allow you to query on the assets table and see all the "objects" that are Assets. If you want to see just the DocumentAsset objects then use document_asset table. So far this schema by default won't create inconsistant Asset objects &em; the asset_id is controlled by a single sequence so if that's enough assurance you can proceed and know that asset_id is unique without explicitly constraining it.
If you really have to ensure that asset_id is unique then you have to step outside of SQL. PostgreSQL has these nice inheritance features but doesn't yet allow you to constrain them declaratively. If you add on this PL/pgSQL code then it will constrain the inheritance for you. All of thsi is obviously just a start but it should get you thinking of the possibilities.
And now to create a PL/pgSQL function and hook it into the right place. Please be aware I just wrote this into a window and didn't try to run it first or even verify that it's syntactically correct. You'll want to read up on PostgreSQL's triggers and look into either implementing this sort of function in PL/pgSQL or maybe it's TCL or Perl implementations.
| [reply] [Watch: Dir/Any] [d/l] [select] |
by djantzen (Priest) on Jan 12, 2003 at 01:57 UTC | |
INHERITS, yes! But unfortunately, I'm not using Postgres. I've got Oracle 8i, and after searching their docs for "inherit", "derive", "subclass", "under" and "child" I can't find an equivalent keyword. All in all that's not a huge loss though, as we've tried to stay DB agnostic in our SQL to as great a degree as possible. What are your thoughts on the principle of the matter as far as using joins here though? pg points out below that denormalization for performance is an accepted practice, but this application isn't really going to be hit that hard. Rather I'm concerned more with ease of maintenance and extensibility, working on the assumption that the child tables will expand down the road. Which, upon reflection, favors joins strongly, views a little less so IMO. Hmmm. /me dreams of INHERITS... | [reply] [Watch: Dir/Any] [d/l] [select] |