CREATE TABLE with REFERENCE

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

i've a little problem with two tables and FOREIGN KEYs. I've read about
this long time ago, but didn't remember me where. Well, I hope you can
help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of creating
than the ALTER TABLE the first table after the second is living?

Not really. That's the correct way to make the constraints.

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating the
first. Otherwise I think you need to be running in a single transaction
(although they could be grouped inside a function or as a trigger for
example).

kay-uwe.genz wrote:

Hi @ all,

i've a little problem with two tables and FOREIGN KEYs. I've read
about this long time ago, but didn't remember me where. Well, I hope
you can help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of
creating than the ALTER TABLE the first table after the second is living?

Its given in documents though.

ALTER TABLE countries ADD CONSTRAINT "refer_city" FOREIGN KEY (capital)
REFERENCES
cities (city) UPDATE CASCADE ;

mind that city must be pkey in cities for it to work.

similarly the other table can be done.

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

Hmm not sure but it could be interesting to experiment..

Show quoted text

regards

Kay-Uwe

kay-uwe.genz wrote:

Hi @ all,

i've a little problem with two tables and FOREIGN KEYs. I've read about
this long time ago, but didn't remember me where. Well, I hope you can
help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of creating
than the ALTER TABLE the first table after the second is living?

No. But what's wrong with ALTER TABLE?

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

No (assuming, that you are talking about inserting a new country and a
capital at the same time, and that the country's capital column cannot
be null).
But what's wrong with transactions?

Dima

Why not just drop the "references" clause? I mean, the point of having
transactions is to guarantee integrity within a transaction, if you're not
going to have that, why even bother with the clause?

Most of my databases don't even user "references", just because I like the
flexibility, and I have multitable keys (keys that can refer to rows from
multiple tables).

Jon

On Mon, 28 Jul 2003, Dmitry Tkach wrote:

Show quoted text

kay-uwe.genz wrote:

Hi @ all,

i've a little problem with two tables and FOREIGN KEYs. I've read about
this long time ago, but didn't remember me where. Well, I hope you can
help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of creating
than the ALTER TABLE the first table after the second is living?

No. But what's wrong with ALTER TABLE?

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

No (assuming, that you are talking about inserting a new country and a
capital at the same time, and that the country's capital column cannot
be null).
But what's wrong with transactions?

Dima

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Jonathan Bartlett wrote:

Why not just drop the "references" clause? I mean, the point of having
transactions is to guarantee integrity within a transaction, if you're not
going to have that, why even bother with the clause?

Quite the opposite - the point is to guaratee the integrity *outside*
the transaction.

You can set the constraints to be 'deferred', so that the referential
integrity only gets verified at the time you commit your transaction-
this way you can allow 'temporary' violations of the constraints inside
your transactions, while still being guaranteed that all the data that
actually gets committed satisfies all of your constraints.

Most of my databases don't even user "references", just because I like the
flexibility, and I have multitable keys (keys that can refer to rows from
multiple tables).

Not much to brag about :-)

Dima

Stephan Szabo wrote:

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

i've a little problem with two tables and FOREIGN KEYs. I've read about
this long time ago, but didn't remember me where. Well, I hope you can
help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of creating
than the ALTER TABLE the first table after the second is living?

Not really. That's the correct way to make the constraints.

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating the
first. Otherwise I think you need to be running in a single transaction
(although they could be grouped inside a function or as a trigger for
example).

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it . istn' it the best way of doing it?
[ if at all it works :-) ]

regds
Mallah.

Show quoted text

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Why not just drop the "references" clause? I mean, the point of having
transactions is to guarantee integrity within a transaction, if you're not
going to have that, why even bother with the clause?

Quite the opposite - the point is to guaratee the integrity *outside*
the transaction.

That's actually what I was saying. Within a single transaction rather
than across multiple transactions. If you have to go across multiple
transactions, there's no real point in having integrity constraints.

Most of my databases don't even user "references", just because I like the
flexibility, and I have multitable keys (keys that can refer to rows from
multiple tables).

Not much to brag about :-)

Do you know of a better way to handle multitable references?

Jon

Show quoted text

Dima

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:

Stephan Szabo wrote:

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating the
first. Otherwise I think you need to be running in a single transaction
(although they could be grouped inside a function or as a trigger for
example).

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it . istn' it the best way of doing it?
[ if at all it works :-) ]

Yes, that'd be the best way (the constraint must be deferrable in that
case and you often want it to be initially deferred for cyclic
constraints). The question seemed to specifically ask if there was a way
without grouping them into a transaction.

correct, in a transatction, **IF** the constraints are declared deferrable and initially deferred.

Rajesh Kumar Mallah wrote:

Show quoted text

Stephan Szabo wrote:

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

i've a little problem with two tables and FOREIGN KEYs. I've read about
this long time ago, but didn't remember me where. Well, I hope you can
help me.

I've create two TABLEs "counties" and "cities". "Countries" have a row
"capital" is REFERENCEd "cities". "cities" have a row country
REFERENCEd "countries", where a save the country the city is placed.

And now PG couldn't create the TABLEs, because the referenced table
doesn't exists in time of creation. Is there another method of creating
than the ALTER TABLE the first table after the second is living?

Not really. That's the correct way to make the constraints.

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating the
first. Otherwise I think you need to be running in a single transaction
(although they could be grouped inside a function or as a trigger for
example).

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it . istn' it the best way of doing it?
[ if at all it works :-) ]

regds
Mallah.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Jonathan Bartlett wrote:

That's actually what I was saying. Within a single transaction rather
than across multiple transactions. If you have to go across multiple
transactions, there's no real point in having integrity constraints.

But why would you have to go accross multiple transactions, when you are
inserting entries into the tables, that are related, and reference each
other?
If one entry doesn't make any sense without the other one, and you
insert it outside a transaction, and then try to insert the other one,
and it fails for any reason, that will leave your database in an
inconsistent state with all those orphaned entries sitting around.
That's exactly what the transactions are invented for - so that you
don't need to worry about the consistency of your data in case of a failure.

Most of my databases don't even user "references", just because I like the
flexibility, and I have multitable keys (keys that can refer to rows from
multiple tables).

Not much to brag about :-)

Do you know of a better way to handle multitable references?

Sure.
SET CONSTRAINTS DEFERRED;
BEGIN;
insert this
insert that
END;

Dima

Show quoted text

Jon

Dima

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

That's actually what I was saying. Within a single transaction rather
than across multiple transactions. If you have to go across multiple
transactions, there's no real point in having integrity constraints.

But why would you have to go accross multiple transactions, when you are
inserting entries into the tables, that are related, and reference each
other?

I have no idea. I wasn't the original poster.

Do you know of a better way to handle multitable references?

Sure.
SET CONSTRAINTS DEFERRED;
BEGIN;
insert this
insert that
END;

That only handles single table references.

For example, I have a database with a "notes" table. This table is used
to store annotations on ANY record within the database on ANY table. I
use a single 64-bit sequence for every key within the database. In fact,
in the GUI framework I ahve set up, in order to add notations to any
screen, I can just add the following code:

note_html($object_id);

and it will insert code to be able to add/update/remove notes on that
object. So, my table NOTES has references to about 5 different tables so
far (it will probably grow with the application), all with the same
column.

There are actually numerous uses of this - I once started writing a paper
on building a generic database structure that could be reusable across
multiple applications, but never had the time to finish it.

Jon

On Tuesday 29 Jul 2003 2:30 am, Stephan Szabo wrote:

On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:

Stephan Szabo wrote:

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the
other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating
the first. Otherwise I think you need to be running in a single
transaction (although they could be grouped inside a function or as a
trigger for example).

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it . istn' it the best way of doing it?
[ if at all it works :-) ]

Yes, that'd be the best way (the constraint must be deferrable in that
case and you often want it to be initially deferred for cyclic
constraints). The question seemed to specifically ask if there was a way
without grouping them into a transaction.

But if the columns are marked NOT NULL it wont' be possible to
insert at the first place itself? i think in such cases one of the
columns must allow NULL , as someone has already pointed out.

regds
mallah.

On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:

On Tuesday 29 Jul 2003 2:30 am, Stephan Szabo wrote:

On Tue, 29 Jul 2003, Rajesh Kumar Mallah wrote:

Stephan Szabo wrote:

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

Second question. Is there a method of INSERT INTO both tables VALUES
without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the
other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating
the first. Otherwise I think you need to be running in a single
transaction (although they could be grouped inside a function or as a
trigger for example).

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it . istn' it the best way of doing it?
[ if at all it works :-) ]

Yes, that'd be the best way (the constraint must be deferrable in that
case and you often want it to be initially deferred for cyclic
constraints). The question seemed to specifically ask if there was a way
without grouping them into a transaction.

But if the columns are marked NOT NULL it wont' be possible to
insert at the first place itself? i think in such cases one of the
columns must allow NULL , as someone has already pointed out.

For doing it in multiple transactions using NULL yes (I mention that
above). You could fake past that by using a non-NULL dummy value that has
a pk row that doesn't have real meaning except for this purpose, but at
that point you probably might as well not bother with the constraint.

Do you know of a better way to handle multitable references?

Sure.
SET CONSTRAINTS DEFERRED;
BEGIN;
insert this
insert that
END;

That only handles single table references.

For example, I have a database with a "notes" table. This table is used
to store annotations on ANY record within the database on ANY table. I
use a single 64-bit sequence for every key within the database. In fact,
in the GUI framework I ahve set up, in order to add notations to any
screen, I can just add the following code:

note_html($object_id);

and it will insert code to be able to add/update/remove notes on that
object. So, my table NOTES has references to about 5 different tables so
far (it will probably grow with the application), all with the same
column.

So, how can you possibly tell when looking at your note which entry it
applies to?
You have your 64-bit id in the note, but how do you know which table to
actually look for that id???
When you delete an object, how do you make sure, that the notes that
refer to it get deleted too?
When you insert a note, how do you know the object it is referring to
exists?
When you insert a new object, how can you be sure there is no object in
another table with the same id?

There are actually numerous uses of this - I once started writing a paper
on building a generic database structure that could be reusable across
multiple applications, but never had the time to finish it.

The common way to do this kind of thing is (depending on the
application, and particular object's properties) either to merge your
five tables into one (possibly, adding an object_type column) or to
split your notes table into five (one for each object table), and then
make the notes reference the appropriate object.

If you want to be really advanced, you might also want to look into the
'inheritance' approach... But I would not recommend that, because
inheritance in sql is rather half-baked - the DDL code for such schema
might look really elegant, but actually working with that database would
be pain in the butt...

Finally, if for some obscure reason you have to have it assymetrical
(one notes tabes referencing several different tables), you can always
write your own trigger to ensure the referential integrity (like the FK
does) against those several tables (you'll still need to have at least
the object type in yoru notes table, so that your trigger knows which
table to check against)...

The worst thing you can do in such situation is - just forget the
constraints, and hope that your app will be able to enforce them on its
own. It won't.

Dima

So, how can you possibly tell when looking at your note which entry it
applies to?

That's easy - these are always referred from the table, never to the
table. In the few instances where I go the other way, it's limited to 2
or 3 tables, and I do separate joins combined with a UNION.

When you delete an object, how do you make sure, that the notes that
refer to it get deleted too?

I only soft-delete items.

When you insert a note, how do you know the object it is referring to
exists?

Because it is always added from my note_html code, which only works for
working objects. Let's say, theoretically, somehow some data got in there
which wasn't attached to anything. First of all, the only way that could
happen is if there was some really broken code, but second of all, what
would the harm be? Obviously it's a bug, but constraints don't prevent
bugs totally either.

When you insert a new object, how can you be sure there is no object in
another table with the same id?

We all use the same sequence.

The common way to do this kind of thing is (depending on the
application, and particular object's properties) either to merge your
five tables into one (possibly, adding an object_type column) or to
split your notes table into five (one for each object table), and then
make the notes reference the appropriate object.

Yes, but the tables have NOTHING to do with each other. I'm not going to
merge my Payments table with my Sponsors table. That would just be nuts.
Splitting the notes table would be pointless. Why do it? The way I have
it set up now, it takes _1 line of code_ to add note-taking capabilities
to my forms. Why would I want to abandon that just to clutter up my
schema? Then, if I want to enhance the note_html interface, I have to
modify the schema in 5 places (that's 5 places so far - as time goes on
this will likely increase to 10 or 15), and possibly have separate copies
of the note_html code. That's craziness. I can't think of one good
reason to do that.

If you want to be really advanced, you might also want to look into the
'inheritance' approach... But I would not recommend that, because
inheritance in sql is rather half-baked - the DDL code for such schema
might look really elegant, but actually working with that database would
be pain in the butt...

It doesn't work for this approach. Inheritance is a single line - my
approach allows you to add "features" to objects at a whim.

Finally, if for some obscure reason you have to have it assymetrical
(one notes tabes referencing several different tables), you can always
write your own trigger to ensure the referential integrity (like the FK
does) against those several tables (you'll still need to have at least
the object type in yoru notes table, so that your trigger knows which
table to check against)...

Again, this would require modifying and testing that trigger every time I
want to add a new thing to take notes on.

The worst thing you can do in such situation is - just forget the
constraints, and hope that your app will be able to enforce them on its
own. It won't.

You base this on.... what exactly?

Jon

Jonathan Bartlett wrote:

In the few instances where I go the other way, it's limited to 2
or 3 tables, and I do separate joins combined with a UNION.

If you can combine your queries with a union, your table layouts must be
very similar if not identical.
Why not put everything into the same table then, and just have an FK
between that table and the notes?

When you delete an object, how do you make sure, that the notes that
refer to it get deleted too?

I only soft-delete items.

What do you mean by soft-delete?
Leaving orphaned notes behind? Bad idea...

When you insert a note, how do you know the object it is referring to
exists?

Because it is always added from my note_html code, which only works for
working objects.

How can you be sure? What if you get hit by a bus, and another admin,
who is not as knowledgeable as you are goes to the db, and runs an
insert with plain sql?
What if, while one connection runs your 'note_html' code, another one
deletes an object you are annotating?

Let's say, theoretically, somehow some data got in there
which wasn't attached to anything. First of all, the only way that could
happen is if there was some really broken code,

Not necessarily. This can easily happen with concurrent transactions
(see above).
Also, even if it was indeed only possible because of a broken code, you
are not saying that your code is bug-free, are you?
Even, if it was, postgres is not, and your filesystem and OS are not
either. If the database crashes in the middle of your insert, you'll end
up having inconsistent data.

but second of all, what
would the harm be? Obviously it's a bug, but constraints don't prevent
bugs totally either.

Constraints don't prevent bugs (nothing does). They *do* prevent data
corruption though, and ensure the consistency of your data.

If I understand your statement ('what would the harm be') correctly, and
you just don't care about your data consistency, then, I guess, you are
rigfht - you don't need any constraints... but, in that case, I don't
think you need a transactional database to begin with. If all you want
from the database is being able to run queries, you are better off
running grep on a bunch of text files, or with some light-weight sql
tool, like mysql or sqllight - either of those will perfrom a lot
better, because they do not bother with the overhead of having to care
about your data consistency, and concurrent access.

When you insert a new object, how can you be sure there is no object in
another table with the same id?

We all use the same sequence.

Right. What if somebody forgets to use that sequence?
What if you load your database from a backup and forget to reinit the
sequence?

The common way to do this kind of thing is (depending on the
application, and particular object's properties) either to merge your
five tables into one (possibly, adding an object_type column) or to
split your notes table into five (one for each object table), and then
make the notes reference the appropriate object.

Yes, but the tables have NOTHING to do with each other.

If that was the case, you would not be able to combine them with a
union, as you said you do...

I'm not going to
merge my Payments table with my Sponsors table. That would just be nuts.

No, it would not. Application logic has nothing to do with your database
schema.
You need to design the schema to ensure effectiveness and reliability.

Then, you design your application on top of it, that handles the
business logic.
From the database perspective, there is no difference between payments
and sponsors, as long as both have the same (or similar) sets of attributes.
Iterpreting those attributes is not database's job.

Splitting the notes table would be pointless. Why do it?

Because that would make it possible to use the constraints.

Also, if one adopts your earlier point, it can also be argued, that it
is equally 'nuts' to have notes about Payments stored together with
notes about Sponsors.
Those notes have just as much to do with each other as the objects they
annotate. :-)
If you insist that Payments must be separate from Sponsors, the same
exact argument should be applied to their respective notes

The way I have
it set up now, it takes _1 line of code_ to add note-taking capabilities
to my forms.

It would *still* be one line of code with either of the approaches I
suggested. Your code doesn't really have to be affected at all
(although, I think, it would really benefit from adding the object_type
argument to your note_html() function, but even that is not necessary)

Why would I want to abandon that just to clutter up my
schema?

You don't want either of that (abandon, or clutter) :-)
You want that same one line of code, working against the properly
designed and normalized sql schema, that lets you rely on the database
top ensure your data consistency and access efficiency.

Then, if I want to enhance the note_html interface, I have to
modify the schema in 5 places (that's 5 places so far - as time goes on
this will likely increase to 10 or 15), and possibly have separate copies
of the note_html code. That's craziness. I can't think of one good
reason to do that.

Not at all. If you give up your idea about splitting your payments from
your sponsors for example, you won't need to modify your schema *at all*
if you need to add another object type, or another kind of note, or
whatever - all you'd need to do would be to implement the new
application logic in your application, where it belongs, and be done
with it. No need to even touch your schema at all.

If you want to be really advanced, you might also want to look into the
'inheritance' approach... But I would not recommend that, because
inheritance in sql is rather half-baked - the DDL code for such schema
might look really elegant, but actually working with that database would
be pain in the butt...

It doesn't work for this approach. Inheritance is a single line - my
approach allows you to add "features" to objects at a whim.

That's exactly what inheritance does (yes, with a single line).
The particular implementations of inheritance in sql have their problems
(as I mentioned earlier), that make me really reluctant from using it,
but being able to add features to your objects, with a single line of
code isn't one of them - to the contrary, it's a *huge* benefit.

The actual problem, in my perspective, is that it kinda encourages you
to use that (inhernetly wrong approach) of treating database tables as
"objects", and columns as "features", and attempt implement your
application logic in sql, which is asking for trouble.

Finally, if for some obscure reason you have to have it assymetrical
(one notes tabes referencing several different tables), you can always
write your own trigger to ensure the referential integrity (like the FK
does) against those several tables (you'll still need to have at least
the object type in yoru notes table, so that your trigger knows which
table to check against)...

Again, this would require modifying and testing that trigger every time I
want to add a new thing to take notes on.

Sure, if you implement in such way. But not if you give it some thought
in advance, and come up with an implementation that would be generic
enough not to care about your application-specific differences between
sponsors and payments :-)

The worst thing you can do in such situation is - just forget the
constraints, and hope that your app will be able to enforce them on its
own. It won't.

You base this on.... what exactly?

How about 15 years of experience? :-)

Dima

NOTE - after writing all this, I did think of a possible solution, but I'm
not sure if PG can handle it. If I made a table called "object" with one
column, the object_id, and then had EVERY table inherit from this table.
Then, I could have my constraints set up against this master table. (I'm
not against constraints - actually for them - but when the database
doesn't support everything I want I'm not going to go heads-over-heals to
do database-based consistency that I can achieve another way).

Anyway, more discussion follows...

If you can combine your queries with a union, your table layouts must be
very similar if not identical.
Why not put everything into the same table then, and just have an FK
between that table and the notes?

No, there are _parts_ that are very similar. I don't know where this
whole "table-combining" kick came from, but I've usually found that it
ends in a disaster.

What do you mean by soft-delete?
Leaving orphaned notes behind? Bad idea...

I have a boolean flag that says "active". I don't ever actually purge
data. There are times when it is useful to come back in and look at
what's been "deleted". From the user-interrface standpoint it has been
deleted, but we can still go back in and retrieve records after they are
gone.

How can you be sure? What if you get hit by a bus, and another admin,
who is not as knowledgeable as you are goes to the db, and runs an
insert with plain sql?

They should read the documentation. If they want to run an insert with
SQL, they should at least be smart about it :) If someone's too stupid to
read documentation, they are going to screw up the database no matter
what.

What if, while one connection runs your 'note_html' code, another one
deletes an object you are annotating?

As I said, I only do soft deletes. So this wouldn't affect anything.

Let's say, theoretically, somehow some data got in there
which wasn't attached to anything. First of all, the only way that could
happen is if there was some really broken code,

Not necessarily. This can easily happen with concurrent transactions
(see above).
Also, even if it was indeed only possible because of a broken code, you
are not saying that your code is bug-free, are you?

If it's not bug-free, having a good database schema isn't going to save
me.

Even, if it was, postgres is not, and your filesystem and OS are not
either. If the database crashes in the middle of your insert, you'll end
up having inconsistent data.

Transactions will handle that one.

If I understand your statement ('what would the harm be') correctly, and
you just don't care about your data consistency, then, I guess, you are
rigfht - you don't need any constraints... but, in that case, I don't

You are missing the point. There are many things that must be balanced:

* Ease of programming / speed of development

* Correctness of code

* Consistency of data

I can get much further with my system on all three points than I can with
yours. With mine, I have a single, tested function that I can use
anywhere. This hits off the first two. While this prevents me from
having a database-checked #3, it still gives me consistent data because we
don't do hard-deletes and we have tested and verified #2. If you can show
me how to get te ease-of-programming and correctness with your approach,
I'd be happy to use it.

think you need a transactional database to begin with. If all you want
from the database is being able to run queries, you are better off
running grep on a bunch of text files, or with some light-weight sql
tool, like mysql or sqllight - either of those will perfrom a lot
better, because they do not bother with the overhead of having to care
about your data consistency, and concurrent access.

I do care about data consistency and concurrent access. And my data is
consistent. I use many of the RDBMS features of postgres such as views /
subselects, transactions, triggers, etc. The fact that I have instances
where they fall short and I have to do it in the application just shows
that our tools are incomplete, not that I don't care about data
consistency. In fact, I have trouble thinking how anyone could have
sensical data with everything joined together into one uber-table, which
you seem to be advocating.

We all use the same sequence.

Right. What if somebody forgets to use that sequence?

We ahve a standard creation process. If someone is an idiot, that can't
be helped. What if someone pulls out several drives from a running RAID?
What if someone inserts bad records (even if they are inconsistent)? What
if someone steals the server?

If you wind up with problems, you have to fix them. The fact that
problems are possible does not make them likely. There is much more
likelihood of a programmer screwing up writing their 14th copy of
note_html and the associated table structure than there is that someone
pulls a number out of their butt. Where would they get the number if not
from the sequence? "Oooh, I know, I'll insert 10 as the primary key, jsut
for kicks! I'll even hardcode that into the app!" Not a very likely
scenario.

What if you load your database from a backup and forget to reinit the
sequence?

If it's being loaded from backup, the schema reinits the sequence on
schema-load.

If that was the case, you would not be able to combine them with a
union, as you said you do...

No, I said that there were certain instances that this happens. Not that
it is the norm. In the cases where I do combine them with a union, I am
only combining the similar parts, not the whole shebang.

No, it would not. Application logic has nothing to do with your database
schema.
You need to design the schema to ensure effectiveness and reliability.

Then, you design your application on top of it, that handles the
business logic.
From the database perspective, there is no difference between payments
and sponsors, as long as both have the same (or similar) sets of attributes.
Iterpreting those attributes is not database's job.

Right, they don't. The only thing they have in common, is that notations
can be added to them. Which is why I have a common notes table, used by
both of them (and several others as well).

Splitting the notes table would be pointless. Why do it?

Because that would make it possible to use the constraints.

Maybe the constraint system should be extended to allow it to check
across multiple tables. The OID type/column is ideal for this. Sadly, it
is only 32 bits, and they are not storing an OID/table lookup like I think
they should. That would enable really powerful database applications that
are way too much work to make today. With that kind of thing, you could
even do record merges with automatic database support.

Also, if one adopts your earlier point, it can also be argued, that it
is equally 'nuts' to have notes about Payments stored together with
notes about Sponsors.
Those notes have just as much to do with each other as the objects they
annotate. :-)

But they are all "notes" on "objects".

If you insist that Payments must be separate from Sponsors, the same
exact argument should be applied to their respective notes

Why? All of the note's attributes function exactly the same, while none
of Payments and Sponsors function the same. Again, you have yet to show
how you can make a mechanism that is as easily extensible as mine is. The
fact that database constraints aren't available to support it is a fault
of the database, not mine.

The way I have
it set up now, it takes _1 line of code_ to add note-taking capabilities
to my forms.

It would *still* be one line of code with either of the approaches I
suggested. Your code doesn't really have to be affected at all
(although, I think, it would really benefit from adding the object_type
argument to your note_html() function, but even that is not necessary)

Hmmm... changing table names, having to go in and recreate an exact copy
of the schema every time that is added.

You want that same one line of code, working against the properly
designed and normalized sql schema, that lets you rely on the database
top ensure your data consistency and access efficiency.

Yes, but to set it up requires a full database change.

Then, if I want to enhance the note_html interface, I have to
modify the schema in 5 places (that's 5 places so far - as time goes on
this will likely increase to 10 or 15), and possibly have separate copies
of the note_html code. That's craziness. I can't think of one good
reason to do that.

Not at all. If you give up your idea about splitting your payments from
your sponsors for example, you won't need to modify your schema *at all*
if you need to add another object type, or another kind of note, or
whatever - all you'd need to do would be to implement the new
application logic in your application, where it belongs, and be done
with it. No need to even touch your schema at all.

I don't see why you want to make my entire database into one giant table.
Why bother with tables at all in that case?

That's exactly what inheritance does (yes, with a single line).
The particular implementations of inheritance in sql have their problems
(as I mentioned earlier), that make me really reluctant from using it,
but being able to add features to your objects, with a single line of
code isn't one of them - to the contrary, it's a *huge* benefit.

First of all, inheritance wouldn't work in case of the notes, as it is a
one-to-many relationship.

No, it's not. At least with PostgreSQL, you can only have ONE line of
inheritance (at least as far as I am aware - please correct me if I'm
wrong). In that case, all of these would have to have a base class of
"notes". Of course, if I wanted to add other similar features, I would be
unable to. For example, if I wanted to add a "changelog" table, and be
able to attach last_modified, creator, etc to certain tables, I could not
do so without adding them to all of them.

The approach I have let's me pick and choose any additional feature I want
to add to any record.

Again, this would require modifying and testing that trigger every time I
want to add a new thing to take notes on.

Sure, if you implement in such way. But not if you give it some thought
in advance, and come up with an implementation that would be generic
enough not to care about your application-specific differences between
sponsors and payments :-)

Actually, they are database-specific.

Jonathan Bartlett wrote:

NOTE - after writing all this, I did think of a possible solution, but I'm
not sure if PG can handle it. If I made a table called "object" with one
column, the object_id, and then had EVERY table inherit from this table.
Then, I could have my constraints set up against this master table.

Ummmm... yeah, that would be a great way to do that *if* the
"inheritance" emulation in sql worked that way ...
Unfortunately, it does not :-(

That's exactly the reason I called it 'half-baked' in one of the earlier
messages... When you inherit table B from table A, B will have all the
columns A has, but setting up an FK on table C against A won't work,
because the FK will check for the key to be present in A *itself*, not
in A or any of its inherited children, as one would expect...

What you *could* do, though, if you really wanted is to kinda emulate
that inheritance on your own, with something like:

create table A
(
id serial primary key,
type text not null
);
create unique index a_idx on A (id,type);

create table B1
(
id int primary key,
type text not null default 'b';
stuff text
foreign key (id,type) references A(id,type) on delete cascade on
update cascade deferrable initially deferred
);

create table B2
(
id int primary key,
type text not null default 'b1',
stuff text,
foreign key (id,type) references A(id,type) on delete cascade on
update cascade deferrable initially deferred
);

create table C
(
id int not null references A on delete cascade on update cascade
initially deferred,
note text
);

... now you can insert notes for either B1 or B2 into C

This kinda works, but just seems like too much trouble to go through -
it would be nice if the 'inheritance' support could do something like
that for you automatically, but, since it doesn't, I'd rather stick with
the old good 'plain sql' solution - get rid of A, merge B1 and B2
together (just B), and make C reference B.

If you can combine your queries with a union, your table layouts must be
very similar if not identical.
Why not put everything into the same table then, and just have an FK
between that table and the notes?

No, there are _parts_ that are very similar. I don't know where this
whole "table-combining" kick came from, but I've usually found that it
ends in a disaster.

Then you need to normalize your schema first - extract those "very
similar" parts, and put them into the single table, and make your notes
table reference that one, then create specialized table(s) on the side,
that will contain those columns that are different between the objects,
and make them reference your 'master' table too.

What do you mean by soft-delete?
Leaving orphaned notes behind? Bad idea...

I have a boolean flag that says "active". I don't ever actually purge
data. There are times when it is useful to come back in and look at
what's been "deleted". From the user-interrface standpoint it has been
deleted, but we can still go back in and retrieve records after they are
gone.

Well... That's the 'GUI delete'...
Sooner or later you will want to do the 'real' delete - either to remove
an object that just was created by mistake, and should not be there at
all, or simply to clean up your database, and remove the stuff that has
been sitting there for years, being 'inactive'

How can you be sure? What if you get hit by a bus, and another admin,
who is not as knowledgeable as you are goes to the db, and runs an
insert with plain sql?

They should read the documentation.

I am sure, they will... *after* they screw up the database, and begin
wonderring what's wrong with it :-)

If they want to run an insert with
SQL, they should at least be smart about it :) If someone's too stupid to
read documentation, they are going to screw up the database no matter
what.

Not really... If your database schema is thoughtfully designed, one has
to be *really* smart to be able to screw something up.

Also, even if it was indeed only possible because of a broken code, you
are not saying that your code is bug-free, are you?

If it's not bug-free, having a good database schema isn't going to save
me.

No, it is not going to save *you*, but it *is* going to save your *data*

Even, if it was, postgres is not, and your filesystem and OS are not
either. If the database crashes in the middle of your insert, you'll end
up having inconsistent data.

Transactions will handle that one.

No, they won't, unless you actually use them :-)

If I understand your statement ('what would the harm be') correctly, and
you just don't care about your data consistency, then, I guess, you are
rigfht - you don't need any constraints... but, in that case, I don't

You are missing the point. There are many things that must be balanced:

* Ease of programming / speed of development

* Correctness of code

* Consistency of data

I can get much further with my system on all three points than I can with
yours. With mine, I have a single, tested function that I can use
anywhere. This hits off the first two. While this prevents me from
having a database-checked #3, it still gives me consistent data because we
don't do hard-deletes and we have tested and verified #2. If you can show
me how to get te ease-of-programming and correctness with your approach,
I'd be happy to use it.

Easy - merge your tables together the way I described above. Your ease
of programming will not be affected - your obj_html() function will
still work, and won't even require any changes. *Moreover* - if you ever
need to add more object types (or modify the existing ones), you will be
able to do that with much less effort, then you would need to invest
now, with your current schema (in most of the cases, you'll be able to
do those kinds of enhancements without even touching your schema *at all*).

I do care about data consistency and concurrent access. And my data is
consistent. I use many of the RDBMS features of postgres such as views /
subselects, transactions, triggers, etc. The fact that I have instances
where they fall short and I have to do it in the application just shows
that our tools are incomplete, not that I don't care about data

No, it does not show that they are incomplete, it shows that you are not
using them the right way :-)

consistency. In fact, I have trouble thinking how anyone could have
sensical data with everything joined together into one uber-table, which
you seem to be advocating.

Not *one* table. I never advocated that. It is perfectly normal to split
your data into different tables *vertically* (i.e. things that do not
have any intersection between their data, should go into different
tables), but it very rarely (if at all) makes any sense to split it
*horizontally* (so that identical columns sit in different tables, just
because your application interprets them differently) - the 'early'
indication of the problems caused by the latter approach is the
temptation to create 'multitable references' - this can always easily be
avoided by eliminating those 'horizontal divisions', and doing so will
necessarily (and immediately) benefit *all three* of your "balancing
points", that you mentioned earlier.

We ahve a standard creation process. If someone is an idiot, that can't
be helped.

Not 'helped' entirely, but the amount of damage *can* and *should* be
limited.
It is an unfortunate reality of life - not all people are smart (even,
some of the ones with access to the database are not).
It is definitely a bad programming practice to assume otherwise.

What if someone pulls out several drives from a running RAID?

What if someone inserts bad records (even if they are inconsistent)? What
if someone steals the server?

The first oen and the last one are hardware related. There are ways to
deal with those too (a good start will be putting a lock on the server
room for example), but they are beyond the current topic.

As for inserting bad records - that's *exactly* what properly designed
constraints are supposed to prevent.

If you wind up with problems, you have to fix them. The fact that
problems are possible does not make them likely. There is much more
likelihood of a programmer screwing up writing their 14th copy of
note_html and the associated table structure than there is that someone
pulls a number out of their butt.

Sure. There should not be copies. Code duplication is a software-world
analog of poorly designed schema problem in the database world.
Both are bad, and should be avoided. I never suggested that you
eliminate the latter at the expense of the former.
Your note_html() looks great to me, and I agree, that it should be kept
that way... it's the database side that look s problematic.

Where would they get the number if not
from the sequence? "Oooh, I know, I'll insert 10 as the primary key, jsut
for kicks! I'll even hardcode that into the app!" Not a very likely
scenario.

Your programmers must be really smart :-)
Are you saying that you have never seen a person writing a piece of sql
like:
insert into mytable (id, data) select max(id) + 1 from mytable, 'mydata'
???

If so, you must be really lucky :-)

What if you load your database from a backup and forget to reinit the
sequence?

If it's being loaded from backup, the schema reinits the sequence on
schema-load.

Exactly. But 'copy from ' does *not* - so, after you have loaded, your
sequnce next_val () will return 1.

If that was the case, you would not be able to combine them with a
union, as you said you do...

No, I said that there were certain instances that this happens. Not that
it is the norm. In the cases where I do combine them with a union, I am
only combining the similar parts, not the whole shebang.

And that's what you should do - similar (common) parts go to the same
table, to which your notes are linked, the differences go to other
tables, and get linked to your master table the same way.
You can then create a bunch of views to make it *look* exactly the same
way as it is now to the programmer, so that your application code will
not be affected at all, but your schema will be nicely normalized, and
you'll be able to set up constraints on it properly.

Splitting the notes table would be pointless. Why do it?

Because that would make it possible to use the constraints.

Maybe the constraint system should be extended to allow it to check
across multiple tables.

Maybe :-)
That was my last suggestion in the original message - you can always
write a trigger to do that...
It would be the worst of the three other possibilities we've considered,
but still better, then what you have now.

The OID type/column is ideal for this.

No, it isn't. Not all tables have oids. The ones that do, do not
guarantee, that they will be unique.
Even if you do that by hand (create unique index on table(oid)), there
is still no way to guarantee their uniqueness across entire database.

Sadly, it
is only 32 bits, and they are not storing an OID/table lookup like I think
they should. That would enable really powerful database applications that
are way too much work to make today.

I don't know what you are talking about :-)
'too much work' ... come on.
If you think of a solution, that is too much work, it does not mean,
that it is the *only* possibility - just think again :-)

With that kind of thing, you could
even do record merges with automatic database support.

What do you mean by "record merges"?
Any meaning of that phrase I can imagine can be easily done with the
currently supported database features... so, you must mean something
different by that, I assume...

Also, if one adopts your earlier point, it can also be argued, that it
is equally 'nuts' to have notes about Payments stored together with
notes about Sponsors.
Those notes have just as much to do with each other as the objects they
annotate. :-)

But they are all "notes" on "objects".

Sure... and all of your payments, sponsors, and whatever else you have
are 'objects', arent' they?
I did not say that your notes have nothing to do with each other - I
only said that they have only as much to do with each other as your
different kinds of objects do. If putting all the objects (or at least,
some parts of them) into the same table doesn't make sense to you, I
don't see why putting the notes together should.
And the other way around - if treating notes in a generic way does make
sense to you, I don't see any reason why doing the same for the
'objects' should not.

If you insist that Payments must be separate from Sponsors, the same
exact argument should be applied to their respective notes

Why? All of the note's attributes function exactly the same, while none
of Payments and Sponsors function the same. Again, you have yet to show
how you can make a mechanism that is as easily extensible as mine is.

I did - just put the damn objects into the same table :-)
It is not just "as easily" extensible as yours, but *much easier*
extensible rather

The
fact that database constraints aren't available to support it is a fault
of the database, not mine.

Your "fault" is that they *are* available, but you just refuse to use
them :-)

Hmmm... changing table names, having to go in and recreate an exact copy
of the schema every time that is added.

I don't know what you are talking about... What table names? What copy
if schema????
You lost me completely.
I *never* suggested anything remotely like that.

You want that same one line of code, working against the properly
designed and normalized sql schema, that lets you rely on the database
top ensure your data consistency and access efficiency.

Yes, but to set it up requires a full database change.

Yep. It does. No argument here.
Frankly, I am not trying to convience you that you should do that
database change now (although, if I were you, I would *certainly* do it
right away, before I get into any more trouble - changing - what 5 - 10
tables? - doesn't look like *that* much of a deal to me, compared to the
benefit you'd by from it).
The discussion was about whether or not your schema was *initially*
created in a good way.

Whether or not to change it now is another question - as I said, I
certainly would, but this is your decision entirely, I am not going to
try to talk you into it :-)

Not at all. If you give up your idea about splitting your payments from
your sponsors for example, you won't need to modify your schema *at all*
if you need to add another object type, or another kind of note, or
whatever - all you'd need to do would be to implement the new
application logic in your application, where it belongs, and be done
with it. No need to even touch your schema at all.

I don't see why you want to make my entire database into one giant table.
Why bother with tables at all in that case?

Not one table - see above.
And I don't understand your second question - "why bother with
tables"... what's the alternative?

First of all, inheritance wouldn't work in case of the notes, as it is a
one-to-many relationship.

That doesn't matter (it indeed won't work 'out of the box' - see the
beginning of this email - but for totally different reason)

No, it's not. At least with PostgreSQL, you can only have ONE line of
inheritance (at least as far as I am aware - please correct me if I'm
wrong). In that case, all of these would have to have a base class of
"notes".

No. They would have a base class of "Object" (or whatever), and the
'notes' would be linked to the Object.
This would in fact, be a *beatiful* solution... it's a shame really that
it doesn't work.
I am wonderring if what postgres does with those inherited FK
constraints is specified by the standard, or if it is just an
implementation feature, that can be improved...

Of course, if I wanted to add other similar features, I would be
unable to. For example, if I wanted to add a "changelog" table, and be
able to attach last_modified, creator, etc to certain tables, I could not
do so without adding them to all of them.

No, you would be perfectly able to add "features" to base class as much
as you want.
That's the beauty of the inheritance.
The "one-to-one" features can just be added as columns to the base table
(you can actually do that with even the current half-baked inheritance
implementation, you can even do that without any inheritance at all -
if all of your common features sit in the same table, like I suggest,
you can just keep adding columns to that table as you like). The
one-to-many ones can go to additional tables, and get linked to the
object id the same way your notes do.

The approach I have let's me pick and choose any additional feature I want
to add to any record.

... sure. Except if you wanted to add a new feature to *all* of the
objects...
Then you would either have to use your "notes hack", and create a
separate table with broken links, or you would have to go and modify
each and every of your "object" tables separately.
That's another reason for you to extract the common features into a
"supertable" - a change like this would then become a breeze.

Again, this would require modifying and testing that trigger every time I
want to add a new thing to take notes on.

Sure, if you implement in such way. But not if you give it some thought
in advance, and come up with an implementation that would be generic
enough not to care about your application-specific differences between
sponsors and payments :-)

Actually, they are database-specific.

No, they are not :-)
The database doesn't have any notion of payments, sponsors, notes etc.
All this stuff is application specific.
Database specific are tables, columns and constraints. Everything else
is application logic, that belongs to the application.

Dima

Not *one* table. I never advocated that. It is perfectly normal to split
your data into different tables *vertically* (i.e. things that do not
have any intersection between their data, should go into different
tables), but it very rarely (if at all) makes any sense to split it
*horizontally* (so that identical columns sit in different tables, just

Okay, so I shouldn't merge the tables then. Let me show you my schema:

Sponsor -> object_id, name, url, representatvie (points to rep table),
city (points to city table), primary contact (points to contact table),
active

Payments -> object_id, sponsor (points to sponsor table), when_paid,
payment_type, payer_contact (points to contact table), company address
(points to addresses table), billing address (points to addresses table),
CC Info (I won't spell it all out for you), amount

Notes -> object_id, noted_object (points to ANY table), note_title,
note_text, note_creation_date, not_creator(points to user table), active

So, since Notes can be attached to any table, I don't see how you are
saying I should combine them, except to combine EVERYTHING into a single
table, and have a value at the beginning to use as the record "type".

No. They would have a base class of "Object" (or whatever), and the
'notes' would be linked to the Object.
This would in fact, be a *beatiful* solution... it's a shame really that
it doesn't work.

As I said, the tool is limitted.

Jon