Thread: Newbie Inheritance Question
Hi All, Can anyone tell me now I might extend an existing record in a parent table to have values in an inherited table? An example would be a table of all people (person), and a table of users (usr) that inherits from person. Say there's an existing record in person, and I want to use that same record to make them a member of the usr table, as well. Is this possible, other than by deleting the record from person and re-inserting it into usr? Thanks, David -- David Wheeler AIM: dwTheory David@Wheeler.net ICQ: 15726394 Yahoo!: dew7e Jabber: Theory@jabber.org
Unless you have some kind of development environment that requires it, you should avoid using inheritance. Besides the fact that I recently asked on the list on how to get indexes to work when selecting from all the inherited tables at once and still have not received an answer, inheritance is just a bad way to structure your database since it makes it a huge pain to manage which entry should be in which table. One of the huge risks of using inheritance as a distinguishing attribute is that if you need to move rows between tables, you have to wrap the move in a transaction to make sure you don't delete something that didn't get moved probably or that you don't have the same row in two different tables. Instead of inheritance, you should have a table such as person_type, which describes the category that they are in which you were formally distinguishing between using inheritance. If you need to add additional columns just for one category, this can be done easily with an extra table, and you can always use views if you don't want to join tables manually a lot or if you need old code to remain compatible with the new layout. By having a column as your category attribute, you also benefit from being able to easily select from child-1 and child-2 without selecting rows from the parent table or child-3. -Edwin On Fri, 22 Jun 2001, David Wheeler wrote: > Hi All, > > Can anyone tell me now I might extend an existing record in a parent table > to have values in an inherited table? An example would be a table of all > people (person), and a table of users (usr) that inherits from person. Say > there's an existing record in person, and I want to use that same record > to make them a member of the usr table, as well. Is this possible, other > than by deleting the record from person and re-inserting it into usr? > > Thanks, > > David > > -- -----BEGIN PGP PUBLIC KEY BLOCK----- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org mQGiBDj905MRBACm5QP3Z4mILmXUsLN8bnhDgoG6HQa4mn/wQsaLApOwN5/802Y8 5yAIZiWH4I2iMqZdqwBrnQXQsoRBWIVHueYoVbtLcLdelt2bW2YI16JYkOMVOQ5i EBd1gP93hsl6Te4sOPQ/X2Oms+MYjMW7u5HPQIv6lIwgjE7MIz19/b+VbwCg671x 9MXIGx9ewF2L4WtqjciaVnkD/jitamiI2bbns37xE2OFWhZSBm+WpMa+HHNGTFtq jwCByZ/t3DT0mKexqvo3EDVha7WgQ3yfzo1GxyAw8x9/9UEVEWlACidGM3vny27I SjqbqKG5IMD5lEU1vEZVZJOe6BFWiAHbRoaBMk/tLCHKZTZOoRywIdCOyyrKBa5E /pKuA/9qObVrvyqiA+RkGHVKH6/R5SKo+YuRTQNmQ0eQtSsTA0Xgl/a72W2B6q7V jeUEaW9CTRTCY+0UFNqq368tSnRBCEsQtLqYB28dnaKZ35AbPNLR+6Fsc1VaKNAS mz9ZhnXtQk3y06skluwdsvjKcLfSLCvgVPMo/fepwRuzfuUwfLQ0RWR3aW4gR3J1 YmJzIChSYWNrc3BhY2UuY29tKSA8ZWdydWJic0ByYWNrc3BhY2UuY29tPohWBBMR AgAWBQI4/dOTBAsKBAMDFQMCAxYCAQIXgAAKCRDXgAoO6t/5UcX7AJ9GlYhmZ+Jc 86FI2+ZouFMemn46IACdH1SclQjEDvApLWYAn6dncmRqgxi5Ag0EOP3UHRAIAMTU Vqbau9aUHSzieDjXVWUUDXitJqtzpPAhgsQWKBqlgSqZtSPr5DN2+OeobcrWhspX +S6VwF/U0iXPTZntng4rN2Y3oUgXjZopjLnfNdWcULvSql8W1JaX/zAc9oQ9uQ8B cc/buFe1ayC3i0rWyTE1bE1A+Lvn8SdeCBEMyp+8Rq3En0hRBitcY4FpEt4ADssM vB4Z71CyBWm9YYD0z9qBteaaaU82+gnoLcryXhc5FSW5HMolMKgHHWmXbMCqMGvH ME0QNRqekewqErW5F6L0Hpf0pPVVXZlcAAmwTJDAdKsZrYB1AEojzv7y6ssf+E2Z oJuKOjzfHBIKwDRIzX8AAwUH/1Y/jqxtJAfLdto3L9XRGXt9zCS5mYCNhEjzkgvE OiqWGJi6g54Umiwf1oizugZcfnbN3l9NHReN37OburXMcNGE754uOatwvfbNny0o W2WCs5GGkjslBe5Xc8a4wMyyr9Dt5NR7muZopEx99W7vHH0gxqXyzcAnkMFcbhdp 86jtot+w1BOa26wjj0keQi0EhbXr5GOTMaHk2avXhzfLYvfVknjYCtScw+4tvtfi n2e4NGzXQxXejHV9uPdDEin7ts1OjmSwB1oXsYVjAU7ZhHQOwDlw5BoUGAorMY0i fHtx16IPwsD+06HFoz9W36iBXuR8GmFXBOiZv8fuMHhTPlOIRgQYEQIABgUCOP3U HQAKCRDXgAoO6t/5USJkAKDOQAywN/ZLijImMIge4ylut9xYyACggOO0sp8QS/ea vFDsdhwocmVhGbo= =qOGJ -----END PGP PUBLIC KEY BLOCK-----
On Fri, 22 Jun 2001, Edwin Grubbs wrote: > Unless you have some kind of development environment that requires it, you > should avoid using inheritance. Besides the fact that I recently asked on > the list on how to get indexes to work when selecting from all the > inherited tables at once and still have not received an answer, > inheritance is just a bad way to structure your database since it makes it > a huge pain to manage which entry should be in which table. One of the > huge risks of using inheritance as a distinguishing attribute is that if > you need to move rows between tables, you have to wrap the move in a > transaction to make sure you don't delete something that didn't get moved > probably or that you don't have the same row in two different tables. > > Instead of inheritance, you should have a table such as person_type, which > describes the category that they are in which you were formally > distinguishing between using inheritance. If you need to add additional > columns just for one category, this can be done easily with an extra > table, and you can always use views if you don't want to join tables > manually a lot or if you need old code to remain compatible with the new > layout. By having a column as your category attribute, you also benefit > from being able to easily select from child-1 and child-2 without > selecting rows from the parent table or child-3. > > -Edwin > If you want to stick to inheritance (which I don't see necessarily as a bad idea) the correct think would be to remove theentry from the parent and insert it in the corresponding child table. So either you use inheritance and let Pg transparently take care of the "relationship" between parent-children or createthe tables with id's so you can connect the corresponding entries with joins to implement the relationship yourself. I guess things get hairy with when you have multiple inheritance, which doesn't seem to be your case. cheers, thalis ps what was the question about "how to get indexes to work when selecting from all the inherited tables at once"? > On Fri, 22 Jun 2001, David Wheeler wrote: > > > Hi All, > > > > Can anyone tell me now I might extend an existing record in a parent table > > to have values in an inherited table? An example would be a table of all > > people (person), and a table of users (usr) that inherits from person. Say > > there's an existing record in person, and I want to use that same record > > to make them a member of the usr table, as well. Is this possible, other > > than by deleting the record from person and re-inserting it into usr? > > > > Thanks, > > > > David > > > > > > -- > -----BEGIN PGP PUBLIC KEY BLOCK----- > Version: GnuPG v1.0.4 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > mQGiBDj905MRBACm5QP3Z4mILmXUsLN8bnhDgoG6HQa4mn/wQsaLApOwN5/802Y8 > 5yAIZiWH4I2iMqZdqwBrnQXQsoRBWIVHueYoVbtLcLdelt2bW2YI16JYkOMVOQ5i > EBd1gP93hsl6Te4sOPQ/X2Oms+MYjMW7u5HPQIv6lIwgjE7MIz19/b+VbwCg671x > 9MXIGx9ewF2L4WtqjciaVnkD/jitamiI2bbns37xE2OFWhZSBm+WpMa+HHNGTFtq > jwCByZ/t3DT0mKexqvo3EDVha7WgQ3yfzo1GxyAw8x9/9UEVEWlACidGM3vny27I > SjqbqKG5IMD5lEU1vEZVZJOe6BFWiAHbRoaBMk/tLCHKZTZOoRywIdCOyyrKBa5E > /pKuA/9qObVrvyqiA+RkGHVKH6/R5SKo+YuRTQNmQ0eQtSsTA0Xgl/a72W2B6q7V > jeUEaW9CTRTCY+0UFNqq368tSnRBCEsQtLqYB28dnaKZ35AbPNLR+6Fsc1VaKNAS > mz9ZhnXtQk3y06skluwdsvjKcLfSLCvgVPMo/fepwRuzfuUwfLQ0RWR3aW4gR3J1 > YmJzIChSYWNrc3BhY2UuY29tKSA8ZWdydWJic0ByYWNrc3BhY2UuY29tPohWBBMR > AgAWBQI4/dOTBAsKBAMDFQMCAxYCAQIXgAAKCRDXgAoO6t/5UcX7AJ9GlYhmZ+Jc > 86FI2+ZouFMemn46IACdH1SclQjEDvApLWYAn6dncmRqgxi5Ag0EOP3UHRAIAMTU > Vqbau9aUHSzieDjXVWUUDXitJqtzpPAhgsQWKBqlgSqZtSPr5DN2+OeobcrWhspX > +S6VwF/U0iXPTZntng4rN2Y3oUgXjZopjLnfNdWcULvSql8W1JaX/zAc9oQ9uQ8B > cc/buFe1ayC3i0rWyTE1bE1A+Lvn8SdeCBEMyp+8Rq3En0hRBitcY4FpEt4ADssM > vB4Z71CyBWm9YYD0z9qBteaaaU82+gnoLcryXhc5FSW5HMolMKgHHWmXbMCqMGvH > ME0QNRqekewqErW5F6L0Hpf0pPVVXZlcAAmwTJDAdKsZrYB1AEojzv7y6ssf+E2Z > oJuKOjzfHBIKwDRIzX8AAwUH/1Y/jqxtJAfLdto3L9XRGXt9zCS5mYCNhEjzkgvE > OiqWGJi6g54Umiwf1oizugZcfnbN3l9NHReN37OburXMcNGE754uOatwvfbNny0o > W2WCs5GGkjslBe5Xc8a4wMyyr9Dt5NR7muZopEx99W7vHH0gxqXyzcAnkMFcbhdp > 86jtot+w1BOa26wjj0keQi0EhbXr5GOTMaHk2avXhzfLYvfVknjYCtScw+4tvtfi > n2e4NGzXQxXejHV9uPdDEin7ts1OjmSwB1oXsYVjAU7ZhHQOwDlw5BoUGAorMY0i > fHtx16IPwsD+06HFoz9W36iBXuR8GmFXBOiZv8fuMHhTPlOIRgQYEQIABgUCOP3U > HQAKCRDXgAoO6t/5USJkAKDOQAywN/ZLijImMIge4ylut9xYyACggOO0sp8QS/ea > vFDsdhwocmVhGbo= > =qOGJ > -----END PGP PUBLIC KEY BLOCK----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
On Fri, 22 Jun 2001, Thalis A. Kalfigopoulos wrote: > If you want to stick to inheritance (which I don't see necessarily as a bad idea) the correct think would be to removethe entry from the parent and insert it in the corresponding child table. > So either you use inheritance and let Pg transparently take care of the "relationship" between parent-children or createthe tables with id's so you can connect the corresponding entries with joins to implement the relationship yourself. > > I guess things get hairy with when you have multiple inheritance, which doesn't seem to be your case. > > > cheers, > thalis > > ps what was the question about "how to get indexes to work when selecting from all the inherited tables at once"? > The problem I've had with using indexes and inherited tables is that selecting from all the inherited tables with "parent*" does not use any indexes even though selecting from "parent" or "child" will use the proper indexes. SELECT * FROM parent* WHERE id = 123;
Edwin Grubbs <egrubbs@rackspace.com> writes: > The problem I've had with using indexes and inherited tables is that > selecting from all the inherited tables with "parent*" does not use any > indexes even though selecting from "parent" or "child" will use the proper > indexes. Update to 7.1. regards, tom lane