Updating 1-n relationships

[skiflyer]

I now have many different chunks of code which update 1-n relationships based on new input... I'll use a simple example just to make the language easier...

I show the user the 5 authors they've assigned to a book... they remove two authors and add one.

I now have a list of 4 authors sent to my processing code... I pretty much always wipe the original 5, and then rewrite the 4.

It just seemed the most reasonable and error proof way to approach the issue... but it occurred to me this is such a common task that there's a chance there's either a standard or a more economical way to do this with SQL.

This applies to a handful of different databases and languages, so I'm really thinking of it in the most general sense.

Thanks,
Rob

 

[harebrain]

I think most people would use predicate logic to delete the rows that are no longer needed, then insert the new row or rows. I.e.,

DELETE FROM table_x WHERE key1 = "book_id" AND key2 IN ["author_id1", "author_id2"]
INSERT INTO table_x ...

 

[oharab]

Or rather than deleting them I would have an active/unactive field so that you could deactivate authors, but still have a record of who worked on the book in the past.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[harebrain]

oharab,

If you were going that route, you'd probably have an Edition entity. Book is 1-n with Edition and Edition would be 1-n with Author. In that case, you'd never delete an Author, but each new Edition of a Book would require all new Author rows.

Data modeling is never as simple as it looks at first glance, is it?

harebrain

 

[oharab]

harebrain:
Nope. Totally agree with you on that!
I'm in the middle of deciphering someones idea of a specification, including some nice looking entity relationship diagrams that mean bugger all!
Still, that's why we get paid all this money I guess!!!!!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[lespaul]

You get paid money for this? I thought it was a charity thing!!!!

Leslie

 

[oharab]

I don't get paid for tek-tips if that's what you're thinking Leslie, I meant at work, though looking at my wage packet each month I'm thinking you may be right about it being for charity!!!

Ben



----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[lespaul]

No, I meant work!!! And my paycheck looks good until I have to spend it and I wonder "Where did it all go???"

Leslie