Quotes Author and Bio's

If you have access to PHPMyAdmin, or something similar, this query could help.
SELECT MAX(LENGTH(name)) FROM quotes_authors

Post here if you have any over 255 characters.

On some of my client's websites, the author's name field contains data in the format 'First Last (xxxx - xxxx)'. That I believe is biographical information as queried in the summary for this issue, but certainly less than Nancy's question about a length greater than 255 characters.

I'm using Quote 7 in migration from a site that used Quotes 6 and I think originally used Quotes 5.

I'm not sure what Quotes was originally intended to do (or can do now if desired), I'm just focusing on the seemingly-simple use on the web site I'm upgrading. On this site, a quote (randomly displayed) has this info:

QUOTE text
AUTHOR text (who said the quote, if this text exists in the database; many quotes have no stated author)
BIO text (brief additional info about the author, if this text exists in the database; most authors lack this)

The display in the Quotes 6 version (struggling to get it in Quotes 7) was like this:

We must not fear daylight just because it almost always illuminates a miserable world.
— Rene Magritte, artist

The quote is the first line. It comes from the Title field of a node of type "quote".
The author line, including em-dash, only shows if there's an author name ("Rene Magritte" in this example).
The bio text, including comma, only shows if there's bio info ("artist", in this example)

Quotes display the above format more or less, though the second and third lines show for only some quotes. Since ALL the values were originally added to the Quotes system, one mystery is how they could have been entered/edited but now are not all editable or displayed.

There are some odd things, not obvious how to clean them up. Starting with, there seem to be three quotes-related tables -- nodes, quotes and quotes_authors -- all with data for (apparently) every quote. The author name seems to be in multiple places, perhaps unnecessarily. I'm not sure whether all the tables are currently used.

TABLE nodes
FIELDS (many)
nid (connects this table to quotes table
type (= quote for a node that is an individual quote)
title (quote text)

TABLE quotes
FIELDS nid (connects this table to nodes table)
aid (connects this table to quotes_authors table)
vid, promote
author (quote author)
citation (more info about the author)

TABLE quotes_authors
FIELDS aid (connects this table to quotes table)
name (quote author)
bio (more info about the author)

It seems that the Quotes display block extracts this...
node->title
quotes->citation
quotes_authors->name
...to present the 3 text chunks displayed in a single quote.

quotes->author seems to be unused, though for many records it has data
quotes_authors->bio seems to be unused, though for many records it has data

One mystery is why the quote author is pulled from TABLE quote_authors when it also exists in TABLE quotes. Or, why citation is pulled from TABLE quotes when a potentially duplicate FIELD bio is in TABLE quotes_authors.

I can imagine different uses for citation and bio, though in my system they seem to have the same data. For some of my quote nodes there's text in citation, for others bio. So, obviously it was possible to edit FIELD bio in the past, but apparently not in Quotes 7. So at this point that data is lost. A remedy would be to copy FIELD bio from TABLE quotes_authors to FIELD citation in TABLE quotes.

Also, there's duplicate data, not sure how it arose or if it is maintained. TABLE quotes has FIELD author with (presumably) the same data as TABLE author_quotes FIELD name, when the only necessary duplication would be key FIELD aid to connect the two tables.

The puzzlement (to me, not knowing the evolution of quotes in this situation) is the multiple tables with somewhat overlapping data, somewhat duplicated data, but quite a bit of "missing" data that is in one table but not another, so can't be edited and doesn't get displayed.

Whatever the current correct structure (tables, fields, values), I need a way to process the tables to get everything where it belongs.

Making THIS difficult to do (or even test) is that there are at least 12,000 quotes!

Suggestions?

Your explanation of Quotes evolution from 5 to 6 explains why the 12,000+ quotes in my system are a mixed bag -- some have quote author "extra" info in bio field, some in citation field.

(By "extra" I mean whatever is desired to be shown along with the quote author name, such as "famous poet" or "(1923-1986)" or "from movie "Casablanca" or whatever. It's not always an actual bio reference or a citation, it's just "extra" or "more" info about the quote and/or the quote author.

Based on what I see around the Web, I'm presuming the typical use of Quotes is for this:
QUOTE
AUTHOR
EXTRA INFO

This could be accomplished by just two tables:

table node has the quote text and an ID identifying the author

then use author ID to do a lookup in... what?

It could be table QUOTES_AUTHORS to get NAME and BIO field text

or...

it could be table QUOTES to get AUTHOR and CITATION field text

But I don't see any reason to use BOTH tables. As Einstein liked to say, a thing should be as simple as possible.

So really, the job is to simplify, reverting to the "best of" Quotes 5 and 6. This raises the question whether switching to entities is appropriate for a simple two-table, edit, lookup and display module. When would any of the three visible fields (quote, author, extra) or the fourth linking field (author ID) ever be used anywhere else? Quotes seems like an isolated module.

I don't see a need to have both tables, whichever is easier to retain should be (probably whichever table is used in the current display of quotes), and the other table eliminated.

(It could be argued that the quote system should not use the nodes table, therefore use entirely separate tables quotes and quotes_authors.)

But before changing the tables architecture, especially before dropping the redundant table, there needs to be a one-time "fixup" process (code or maybe just a SQL statement) to locate "extra" info in the obsolete table and copy it to the other table.

And, the quote edit form must allow full editing of a quote -- the quote text, and its author name and extra info fields. Plus, there should be a way to directly edit the author name and extra info (to then be reflected in the display of any/all quotes by this author).

Another requirement is that all three fields are accessible in forms and views, so the display of quotes can be customized as desired.

The final question is about the node that stores the quote text. In my system (a mix of Quotes 5 and 6 data) it's always in the node Body field, and sometimes also in the node Title field. Putting the same text in both fields might be useful since Title can be shown in views used to manage quotes (Body can't), while Body can contain more text so can handle virtually any length quote. But updating both fields should be automated via the quote creation/editing form -- type the quote in Body and upon save it will also be put into Title (truncated if necessary). In the actual display of a quote, pull from the Body field.

I hope these thoughts are useful, if incomplete.

That's a fair description of the process. One of the reasons for moving the author into it's own table was that several people, including me, were seeing, for example, "John Smith," "John J. Smith," and "John Smith, Ph. D." (It certainly sounds as though Hawkdrupal is in that boat.) It was an effort to help eliminate duplicates; the additional table helped allow the capability of autocomplete. The base table should not have an duplicate column, but a pointer (author id - aid) to the author table.

The addition of the Bio field had some parallels to this in that people were using the Citation field to contain some biographical information and running into text field limits.

I have considered building on Charles' D7 conversion and moving to Fields (probably by way of a Feature implementation), but there is much going on in my life right now so it still remains a twinkling in my eye, so to speak. I have no doubt that the rebuilding and patching have, over the years, left some crud that should be cleaned up.

Another part that would help is moving the bulk import to Feeds or Migrate, as there is some confusing code relating to that capability.

Using nodes also gives the capability for revisions, which may or may not have value, but surely someone would have asked for it.

By the way, using Fields will result in several more tables, not fewer.

One of the things that many people don't see unless they contribute to DO themselves is that one must not think of only the way one wishes to use the module. Other people will surprise you in how they use it.

Thanks for the enlightening response.

Sorry if my commentary is semi-informed. While I'm a (very) experienced developer of database applications, it was on other platforms/environments. I have almost no experience with the guts of Drupal so I'm just stumbling around. So my thoughts are more about Quotes usage and general structure, with ignorance of plumbing or constraints.

My sole "argument" is that Quotes could/should be simple, not complex -- do one thing really well. I know people can dream up all kinds of wishes, but again wandering the web, the vast majority of quote presentations I see are Quote, Author, Extra Info -- 3 simple fields (plus a fourth key field) involving 2 tables.

My office has a large collection of "famous quotations" books and they all use the simple format of Quote, Author, Extra Info (so I can only wonder what else some users might wish for). I'm assuming that if there's an important need beyond the basic 3 chunks of text, at least one of the books would have used it. (Actually, there is one more data value some books use by implication, a category/topic value used to organize/group/select quotes; for those who need it, this can be handled by Quotes using a Taxonomy field added to the quotes node type.)

At this point, Quotes 7 works, but seems to omit certain data entered/used by prior versions (in my case, thousands of quotes with bio/citation type data that is no longer visible or editable). If that "mess" could be cleaned up (perhaps as described in my prior post), Quotes would be fully functional for maybe for 98% of situations (including mine).

When I started with Quotes (D5) there was no Citation ("extra info") field and on my site, I wanted that. In the process of adding that, I had to modify the bulk import code. I'd like to think I improved it. When D6 came out, menu handling changed considerably, breaking the bulk import again. I got it working but was never totally happy with it but never cleaned it up. When D7 came out, I was not in a position to do much work at all (almost dying). Charles stepped up to the plate, for which I am grateful. As I said, I still think about what to do to get to version 2 (Fields) and recent work with Features has re-ignited those thoughts. If I can ever start on it, I suspect that there would be a lot of clean up done and I suspect the module would become much simpler.

Files sent as requested.