On 20 Sep 2014, at 11:09pm, Mark Halegua <phanto...@mindspring.com> wrote:
> I'm going to have a separate table for pennames. Lester del Rey will be last > name del Rey, > first name Lester. > > My problem will be the following: > > Therer are a number of writers who used several non de plummes, however there > are some > credited authors who are house names (Maxwell Grant is one, with no fewer > than three > writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted > Tinsley, and > others) and it is this I'm having trouble normalizing. Thinking for those > house names I'm > going to have to refer to the story or issue to fully credit the corect > writer, Any thoughts > here are welcome. I suspect that you may need a many-to-many relationship. You have already listed a case where one author used many pen-names, and a case where different people wrote with the same pen-name. To make it worse, some works are written by someone writing under their own name. Inconceivable. If you need to capture the whole truth in your data columns, taking into account all combinations which happened, no matter how rare, then each work published needs two fields: real author and pen-name. Each of which, of course, should be foreign keys into two other files. Often the texts shown in those two fields will match each-other. But one might be an informal version of the other, for example "Francis H. Burns" in the person file but "Frank Burns" in the pen-name file because that's the way the name is usually printed. Another way to capture the whole truth would be to establish the many-to-many relationship between people and pen-names, and have each work point at a record in the middle file of that relationship. If you only need to capture 99% of the truth, and can insert a note for a few works explaining anything unusual, then it may be possible to store only the pen-name for each work. In your pen-name file you can list what would normally be just the name of the one person who used that pen-name, however you might want to establish a format where there could be multiple names separated by commas or semicolons or something. I hate making choices like these. Good luck picking one which turns out to be the one your users will find most convenient. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users