Re: [SQL] Triggers on system tables

2010-03-03 Thread Andreas Kretschmer
Gianvito Pio wrote: > Hi all, > is there a way (also strange) to define a trigger on a system table (for > example on pg_class)? No. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If

[SQL] Triggers on system tables

2010-03-03 Thread Gianvito Pio
Hi all, is there a way (also strange) to define a trigger on a system table (for example on pg_class)? I tried but I got (as expected) a permission denied message... Thanks

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 10:29:44AM -0600, Little, Douglas wrote: > Louis, > Interesting discussion. Always fun to think about real world stuff. Indeed. > We have a similar problem for comparing hotel rooms. > So the issue is that you aren't originating the data, just classifying it. > I'd mov

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Little, Douglas
Louis, Interesting discussion. Always fun to think about real world stuff. We have a similar problem for comparing hotel rooms. So the issue is that you aren't originating the data, just classifying it. I'd move toward a scheme where you reclassify the line marketing speak to common lay terms

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 10:13:48AM -0600, Little, Douglas wrote: > Hey Louis, > > Ship rooms are just like Hotel rooms. There are lots of ways to > describe. But there needs to be some consistency between the > classifiers for them to have any meaning. > > A junior suite should mean the same th

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 04:05:43PM -, Oliveiros wrote: > > As your table names seem to imply, type and category are cabin's > characteristics , not ship characteristics. > Am I right? Yes and no :) - I'm scanning cruise line web sites with a spider to collect prices so I'm building cabin_

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Little, Douglas
Hey Louis, Ship rooms are just like Hotel rooms. There are lots of ways to describe. But there needs to be some consistency between the classifiers for them to have any meaning. A junior suite should mean the same thing regardless of the ship it's on. Doug -Original Message- Fro

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 10:03:05AM -0600, Little, Douglas wrote: > Hello, > > I would have designed as ship > cabin (PK of ship_id, Cabin_id) > And a separate chain of cabin_type > cabin_category > cabin Ah, now I'm having second thoughts about my schema ;) > Type, and category are group classi

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Oliveiros
- Original Message - From: "Louis-David Mitterrand" To: Sent: Wednesday, March 03, 2010 3:38 PM Subject: Re: [SQL] check constraint on multiple tables? On Wed, Mar 03, 2010 at 07:29:22AM -0800, Richard Broersma wrote: On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand wrote:

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Little, Douglas
Hello, I would have designed as ship > cabin (PK of ship_id, Cabin_id) And a separate chain of cabin_type > cabin_category > cabin Type, and category are group classifiers and shouldn't be used to define the uniqueness of a cabin. Take an example where the cabin category and type are defined

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Richard Broersma
On Wed, Mar 3, 2010 at 7:38 AM, Louis-David Mitterrand wrote: > Each ship is different and has specific cabin types and categories. Of > course there is some overlap between ships but I thought it simpler (or > more elegant) to use that hierarchy. Maybe my schema is wrong? I'm probably ignorant

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 07:29:22AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand > wrote: > > > CREATE TABLE cabin_type ( > > > CREATE TABLE cabin_category ( > > > CREATE TABLE cabin ( > > I'm just curious about a few things. > > 1) What is the differ

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Richard Broersma
On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand wrote: > CREATE TABLE cabin_type ( > CREATE TABLE cabin_category ( > CREATE TABLE cabin ( I'm just curious about a few things. 1) What is the difference between a cabin_type and a cabin_category. 2) Does each ship have an exclusive set

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
On Wed, Mar 03, 2010 at 07:14:29AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand > wrote: > > > > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > > with a join down to 'ship'? (if possible). > > Can you post simplified table defi

Re: [SQL] check constraint on multiple tables?

2010-03-03 Thread Richard Broersma
On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand wrote: > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > with a join down to 'ship'? (if possible). Can you post simplified table definitions for the relations involved? -- Regards, Richard Broersma Jr. Visit the

[SQL] check constraint on multiple tables?

2010-03-03 Thread Louis-David Mitterrand
Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category --> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I adde