Thread: CREATE TABLE with REFERENCE
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? Second question. Is there a method of INSERT INTO both tables VALUES without group them in the same Transaction? regards Kay-Uwe
Attachment
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
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.. > > > regards > > Kay-Uwe
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).
Stephan Szabo wrote:
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.
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: >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
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: > 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 >
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: > 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 >> >> >
> >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 > > 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 >
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 >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
> 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 :-) > I would never hire such a person. > Exactly. But 'copy from ' does *not* - so, after you have loaded, your > sequnce next_val () will return 1. We just use pg_dump, which re-sets the sequence to its proper value. > >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. I wasn't indicating that the current implementation was ideal. In fact, I indicated exactly the opposite. I was talking about the idea behind it. > >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... Kind of. Lets say that you build a commodity database application, which has customer tables, invoice tables, etc. Let's say you had two customers, A and B, who merged, and you wanted to merge their records together. You could write a program to do it, but it would be specific to customer records, and if other kinds of record merges were needed you would have to write separate programs for those (say, contact merges or something). So, you have to write a custom application for every type of record merge, and it won't even attempt to take into account any custom tables taht someone else defines. Let's say that instead you used the following pattern when building your database: * All rows had OIDs * All foreign keys that related to OIDs had a specific, OID type (not just generic integer) Now, if you want to merge record 1345 with record 1765, and you wanted 1765 to be the new master, you could do the following: Search the database catalog for columns of type OID. For each instance, update all rows having 1345 to have 1765 instead Not each instance this generates an exception If successful, great, if not, report back which rows had integrity problems after the merge. Mark record 1345 as being deleted. In addition, you could have a generic "merge" table which recorded every record and what record it was merged into. With this, you can apply this generic merge function to any record of any table at all, and it will continue to work in user-defined custom modules. > 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. Hmm, on the one hand you think this is a beautiful solution, but on the other hand you reject my notion that the database does not have all the power it could? > 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... I'm not sure that inheritance is part of any standard.
> 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
Interesting. That might actually be doable. Thanks! Jon On Tue, 29 Jul 2003, Dmitry Tkach wrote: > Jonathan Bartlett wrote: > > >>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". > > > > > Well... this is not the ideal solution (ideally, your > contacts/reps/addresses/cities would need to be rethought somewhat too), > but something you could use as an illustration of what I am talking about... > > create table entity > ( > id serial primary key, > name text not null, > contact int not null references contacts, > address int not null references addresses, > active boolean, > insertstamp timestamp > ); > > create table sponsor > ( > id int primary key references entity, > rep int references rep, > url text > ); > > create table payment > ( > id int primary key references entity, > sponsor int references sponsor, > type int, > billing_address int references addresses, > not_spelled_out_info text > ); > > create table notes > ( > if int primary key references entity, > object_id int not null references entity, > title text, > body text > ); > > ... to be really thorough, the entity should also have something like > object_type on it, that should be included into the FKs, to make sure > you cannot create, say, a payment, and a sponsor with the same id... > Also, contacts/reps/users, should be in the same table (linked to > entity) as well ... etc... > > But, as I said, this seems to be a fairly clear illustration of the > approach... > > Dima > > > > > > > > > > > > > > >>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 > > > > > > >
Jonathan Bartlett wrote:
>>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".
>
>
Well... this is not the ideal solution (ideally, your
contacts/reps/addresses/cities would need to be rethought somewhat too),
but something you could use as an illustration of what I am talking about...
create table entity
(
id serial primary key,
name text not null,
contact int not null references contacts,
address int not null references addresses,
active boolean,
insertstamp timestamp
);
create table sponsor
(
id int primary key references entity,
rep int references rep,
url text
);
create table payment
(
id int primary key references entity,
sponsor int references sponsor,
type int,
billing_address int references addresses,
not_spelled_out_info text
);
create table notes
(
if int primary key references entity,
object_id int not null references entity,
title text,
body text
);
... to be really thorough, the entity should also have something like
object_type on it, that should be included into the FKs, to make sure
you cannot create, say, a payment, and a sponsor with the same id...
Also, contacts/reps/users, should be in the same table (linked to
entity) as well ... etc...
But, as I said, this seems to be a fairly clear illustration of the
approach...
Dima
>
>
>>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
>
>
Jonathan Bartlett wrote: >>Exactly. But 'copy from ' does *not* - so, after you have loaded, your >>sequnce next_val () will return 1. >> >> > >We just use pg_dump, which re-sets the sequence to its proper value. > > Lucky you :-) But wait, till your database grows beyond a few hundred gig... > > >>>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. >> >> > >I wasn't indicating that the current implementation was ideal. In fact, I >indicated exactly the opposite. I was talking about the idea behind it. > I even kept your original quote above - you *did* say it was "ideal" in those exact words :-) I wasn't dreaming :-) > > > >>>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... >> >> > >Kind of. Lets say that you build a commodity database application, which >has customer tables, invoice tables, etc. Let's say you had two >customers, A and B, who merged, and you wanted to merge their records >together. You could write a program to do it, but it would be specific to >customer records, and if other kinds of record merges were needed you >would have to write separate programs for those (say, contact merges or >something). So, you have to write a custom application for every type of >record merge, and it won't even attempt to take into account any custom >tables taht someone else defines. > If your schema was properly designed to begin with, you should not even need any application at all - just do: begin; set constraints all immediate; update users set id=<new_userid> where login = 'customerA'; set constraints all deferred; delete from users where login = 'custmerA'; update users set id=<new_userid> where login = 'custmerB'; delete from users where login = 'customerB'; insert into users values (<new_id>, 'merged_customer_login', ...); commit; This should take care about rerouting all the depending entries to the new user *as long as you have your FKs setup properly*, of course. > >Let's say that instead you used the following pattern when building your >database: > >* All rows had OIDs >* All foreign keys that related to OIDs had a specific, OID type (not just >generic integer) > >Now, if you want to merge record 1345 with record 1765, and you wanted >1765 to be the new master, you could do the following: > >Search the database catalog for columns of type OID. >For each instance, update all rows having 1345 to have 1765 instead >Not each instance this generates an exception >If successful, great, if not, report back which rows had integrity >problems after the merge. >Mark record 1345 as being deleted. In addition, you could have a generic >"merge" table which recorded every record and what record it was merged >into. > >With this, you can apply this generic merge function to any record of any >table at all, and it will continue to work in user-defined custom modules. > > See above - all this is *easier* done with just regular FKs - no need to lookup catalogs, reporting integrity problems, blah, blah, blah... All you need is to set up your FKs correctly, so that the DB knows your integrity rules - everything else is just done 'automagically' for you 'under the hood'. > > >>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. >> >> > >Hmm, on the one hand you think this is a beautiful solution, but on the >other hand you reject my notion that the database does not have all the >power it could? > > I don't reject your notion. Database does luck power in many areas - just not in the ones you are complaining about :-) > > >>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... >> >> > >I'm not sure that inheritance is part of any standard. > > Me neither :-) But, I think I heard somewhere that SQL99 has something about it... Dima
> begin; > set constraints all immediate; > update users set id=<new_userid> where login = 'customerA'; > set constraints all deferred; > delete from users where login = 'custmerA'; > update users set id=<new_userid> where login = 'custmerB'; > delete from users where login = 'customerB'; > insert into users values (<new_id>, 'merged_customer_login', ...); > commit; > > This should take care about rerouting all the depending entries to the > new user *as long as you have your FKs setup properly*, of course. Interesting. After reading this I went back to the docs and found what I haven't found before: "Analogous to ON DELETE there is also ON UPDATE which is invoked when a primary key is changed (updated). The possible actions are the same." I was under the impression that cascades only applied to deletes. Something new to chew on. Thanks! Jon