Okay, here's my problem. I have lengthy, say, articles and they have one, two, or more authors. If I'm keeping everything in a mySQL db and want to be able to search by author, how would you recommend I go about this? For example:
author = ("Owen", "John");
author = ("Owen");
I want to still be able to search for articles by Owen or by John. I though about doing a set, but there's about 500 different "authors" so that wouldn't really work :)
Anyone have any ideas. I'm guessing there's a datatype for something like this, though I couldn't find it on the mySQL reference website. (I really need to buy a book.)
You could do it a couple of ways:
WHERE author = "Owen" OR author = "Bob";
Or you could do:
WHERE author IN( "Owen", "Bob" );
That's not quite what meant. I didn't mean searching for two names, but for storing a variable number of names in a record... So maybe an article has one or maybe two authors. I want to be able to store any number of authors in each record. I don't really want to make another db because 95% would only have one.
I was thinking about storing the names like "owen|bob"...
Oh well to do it the "real" way you'd want something like:
You CAN stuff the names into a varchar column if you want and then use PHP to explode those out, but then you're really doing a "badthing" in table design. You're misusing the whole idea of relational db design when you stuff things like that.
CREATE TABLE articles AS(
CREATE TABLE authors AS(
CREATE TABLE author_article AS(
PRIMARY KEY( author_id, article_id )
FROM article a,
WHERE a.article_id = aa.article_id;
NOTE: You can replace author_id with author_name if you want to use the text name of an author instead of some ID (such as SSN or something). The only problem is that you could have "Bob" in there and you'd be forced to call the other author "Bob1" or something.
MattR's example is ideal. Read up a bit on relational database design and you should be able to avoid problems like this in the future. Here's a good tutorial: