Re: Common Pattern for parent-child INSERTs?
Hello Jan,
On 1/7/2012 00:58, Jan Steinman wrote:
Okay, I'm seeking enlightenment here.
In my trivial case, one can save storage by not having a record extension for
people without phones. Big deal.
In my real-world case, I have a contacts database with your typical name, address, phone, email, etc. info.
Then I have extensions for people who are particular type of contacts that have more information than the
general case. If I have several thousand records in my contacts database, but only ten in the dairy
customers database, I'm saving a ton of storage by not having every single record in the
general-purpose contacts database contain stuff like desired_milk_pickup_day or SET
dairy_products_of_interest.
But now I have a different extension, Volunteers, with extra fields like special_skills,
dietary_restrictions, etc. I don't want those fields in the general contact list. And there's another
extension, Advisory, that holds extra information for contacts who are on our advisory council.
In normalizing databases, I was taught to do exactly what I've done, separate
out the special cases and put them in a separate table. But as you note, that
creates a bit of a mess for INSERT while simplifying SELECT.
ON UPDATE CLAUSE does not help on INSERT, does it? I mean, how does it know the
auto-increment value of the parent record before it's been INSERTed?
It appears that anything I do must be wrapped in a transaction, or there's the
chance (however unlikely) that something will get in between the INSERT of the
parent and that of the child.
Once you have inserted the 'parent' row (the one to the Contacts table)
you know the ID of the parent. This cannot change and no other contacts
will be given the same ID. You include this ID with the other INSERT
commands you need for your 'child' rows.
You do have two options to handle rollback scenarios:
1) run with only InnoDB tables and wrap all of the related INSERTs with
a single transaction
2) use any tables you like and keep track of the auto_increment values
issued for each row you are INSERTING in your application, too. This
allows you to implement a manual rollback in the event of some kind of
problem.
Take, for example, your Volunteers example. This requires at least two
rows: one main row on the `contacts` table and another on the
`volunteers` table.
INSERT `contacts` (name, ...) VALUES ('Bob the Volunteer',...);
SET @contact_id = LAST_INSERT_ID();
INSERT `volunteers` (contact_id, skill, diet_restrict, ...) VALUES
(@contact_id, 'carpentry', 'hates fish', ...);
SET @volunteer_id = LAST_INSERT_ID();
...
If you don't want to track the ID values in user variables, you can
query them and draw them back into application-based variables. If you
want to track lists of values, you can add them to temporary tables to
build each list or query them into application-side arrays. At this
point how you handle those numbers is up to you.
Remember, though, that LAST_INSERT_ID() can only return one value. This
means that you cannot use it for batch processing reliably unless you
manually lock the table and guarantee a specific sequence of numbers.
The other option is to build an association table of (id, name) based on
the newly-inserted data (or something similar based on some unique
combination of identifiers in your original data instead of just 'name'
) so that each of your child rows can be assigned their proper parent id
values.
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Okay, I'm seeking enlightenment here. In my trivial case, one can save storage by not having a record extension for people without phones. Big deal. In my real-world case, I have a contacts database with your typical name, address, phone, email, etc. info. Then I have extensions for people who are particular type of contacts that have more information than the general case. If I have several thousand records in my contacts database, but only ten in the dairy customers database, I'm saving a ton of storage by not having every single record in the general-purpose contacts database contain stuff like desired_milk_pickup_day or SET dairy_products_of_interest. But now I have a different extension, Volunteers, with extra fields like special_skills, dietary_restrictions, etc. I don't want those fields in the general contact list. And there's another extension, Advisory, that holds extra information for contacts who are on our advisory council. In normalizing databases, I was taught to do exactly what I've done, separate out the special cases and put them in a separate table. But as you note, that creates a bit of a mess for INSERT while simplifying SELECT. ON UPDATE CLAUSE does not help on INSERT, does it? I mean, how does it know the auto-increment value of the parent record before it's been INSERTed? It appears that anything I do must be wrapped in a transaction, or there's the chance (however unlikely) that something will get in between the INSERT of the parent and that of the child. On 5 Jan 12, at 05:51, Johan De Meersman wrote: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html If you use InnoDB for your tables, you can use the ON UPDATE CASCADE option for your foreign key constraints. However, your habit is indeed a nasty one :-p It forces you to do joins where none are necessary, thus needlessly slowing down operations. The oo extends idea does not exactly match what you're doing, as the child table you create does not inherit the parent's attributes, it merely has it's own column and a reference to the parent. This is good for 1:n relations, but for 1:1 there are only downsides (except for a very few edge cases). - Original Message - From: Jan Steinman j...@bytesmiths.com To: mysql@lists.mysql.com Sent: Thursday, 5 January, 2012 1:12:15 AM Subject: Common Pattern for parent-child INSERTs? Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel An idea that is not dangerous is unworthy of being called an idea at all. -- Oscar Wilde Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html If you use InnoDB for your tables, you can use the ON UPDATE CASCADE option for your foreign key constraints. However, your habit is indeed a nasty one :-p It forces you to do joins where none are necessary, thus needlessly slowing down operations. The oo extends idea does not exactly match what you're doing, as the child table you create does not inherit the parent's attributes, it merely has it's own column and a reference to the parent. This is good for 1:n relations, but for 1:1 there are only downsides (except for a very few edge cases). - Original Message - From: Jan Steinman j...@bytesmiths.com To: mysql@lists.mysql.com Sent: Thursday, 5 January, 2012 1:12:15 AM Subject: Common Pattern for parent-child INSERTs? Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Common Pattern for parent-child INSERTs?
Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Hi Jan, I am not sure to understand what your question is, what do you mean with inserting updating 2-3 tables? I guess treat the 3-tables join as one single 'object' ? Since you have the referential integrity constraint on the [addresses] and [phones] table you need to follow this basic pattern: INSERT: 1.insert the record into [names] 2.insert the records into [addresses] and [phones] DELETE: 1.delete the records from [addresses] and [phones] 2.delete the record from [names] UPDATE: (a)no problem if you don't update the foreign keys (i.e. assigning an address and/or a phone number to another person) (b)if you need to update the foreign keys just make sure you set them to an existing names_id The problem you mention with the view is probably coming from the fact that when you insert into a view although theoretically possible if the underlying select is a simple multi-table join (updatable view) you have no assurance on the order of the inserts inside the view, it is probably depending on the specific storage engine implementation. I hope this shed a bit of light. Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Thanks, Claudio. What you suggested is essentially what I'm doing. I just thought if this were something common, someone would have a better way of doing it. I would LOVE to be able to simply insert into a names-addresses-phones VIEW, but I haven't been able to make that work. On 4 Jan 12, at 16:48, Claudio Nanni wrote: Hi Jan, I am not sure to understand what your question is, what do you mean with inserting updating 2-3 tables? I guess treat the 3-tables join as one single 'object' ? Since you have the referential integrity constraint on the [addresses] and [phones] table you need to follow this basic pattern: INSERT: 1.insert the record into [names] 2.insert the records into [addresses] and [phones] DELETE: 1.delete the records from [addresses] and [phones] 2.delete the record from [names] UPDATE: (a)no problem if you don't update the foreign keys (i.e. assigning an address and/or a phone number to another person) (b)if you need to update the foreign keys just make sure you set them to an existing names_id The problem you mention with the view is probably coming from the fact that when you insert into a view although theoretically possible if the underlying select is a simple multi-table join (updatable view) you have no assurance on the order of the inserts inside the view, it is probably depending on the specific storage engine implementation. I hope this shed a bit of light. Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio If they can get you asking the wrong questions, they don't have to worry about the answers. -- Thomas Pynchon Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Common Pattern for parent-child INSERTs?
Jan, Just thinking out loud. In relational theory views are just like any entity, in this case the referential integrity would be with the same logical entity, and in the moment of the insert the referential integrity constraint is violated because the new [names_id] will be present only after the operations is done. It might still be storage engine dependent and how it implements an insert on a updatable view. This said, at your own risk, you can disable the foreign key checks before the insert and re-enable them after. At your own risk. Cheers Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Thanks, Claudio. What you suggested is essentially what I'm doing. I just thought if this were something common, someone would have a better way of doing it. I would LOVE to be able to simply insert into a names-addresses-phones VIEW, but I haven't been able to make that work. On 4 Jan 12, at 16:48, Claudio Nanni wrote: Hi Jan, I am not sure to understand what your question is, what do you mean with inserting updating 2-3 tables? I guess treat the 3-tables join as one single 'object' ? Since you have the referential integrity constraint on the [addresses] and [phones] table you need to follow this basic pattern: INSERT: 1.insert the record into [names] 2.insert the records into [addresses] and [phones] DELETE: 1.delete the records from [addresses] and [phones] 2.delete the record from [names] UPDATE: (a)no problem if you don't update the foreign keys (i.e. assigning an address and/or a phone number to another person) (b)if you need to update the foreign keys just make sure you set them to an existing names_id The problem you mention with the view is probably coming from the fact that when you insert into a view although theoretically possible if the underlying select is a simple multi-table join (updatable view) you have no assurance on the order of the inserts inside the view, it is probably depending on the specific storage engine implementation. I hope this shed a bit of light. Claudio 2012/1/5 Jan Steinman j...@bytesmiths.com: Having been steeped in object-orientation, I have a nasty habit of creating parent-child tables that have a 1:1 relationship where the child extends the parent, sometimes to a depth of three or more. For example: CREATE TABLE names TYPE InnoDB id INT NOT NULL AUTO INCREMENT PRIMARY KEY, name_first VARCHAR(255) NOT NULL, name_last VARCHAR(255) NOT NULL CREATE TABLE addresses TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) street VARCHAR(255) NOT NULL, city VARCHAR(255) NOT NULL CREATE TABLE phones TYPE InnoDB names_id INT NOT NULL REFERENCES names (id) phone VARCHAR(255) NOT NULL (Keyed in from memory for schematic purposes, may contain errors. CREATE syntax is not what I'm here about.) Now how do I go about INSERTing or UPDATEing two or three tables at once in a way that maintains referential integrity? I've tried making a VIEW, but I wasn't able to INSERT into it. I don't think I was violating the restrictions on VIEWs as stated in the manual. Is there a generalized pattern that is used for INSERTing and UPDATEing these parent-child tables? Does it require a TRIGGER in order to propagate the foreign key? (BTW: MySQL version 5.0.92, if that matters...) Thanks in advance for any help offered! Security is mostly a superstition. Security does not exist in nature, nor do the children of men as a whole experience it. Avoiding danger is no safer in the long run than outright exposure. Life is either a daring adventure, or nothing. -- Helen Keller Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- Claudio If they can get you asking the wrong questions, they don't have to worry about the answers. -- Thomas Pynchon Jan Steinman, EcoReality Co-op -- Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
