Question about relational DB design

I'm new here, so hello everyone!

I'm not an experienced relational database designer but I'm not *completely* novice either. Now, I'm facing an urgent question that isn't covered (as far as I remember) by the litterature I've read up to this point.

Using mySQL, I'm building a fairly complex database, of which one of the tables registers article authors. The problem is, I have to deal with two different "species" of authors : the ones that are members of the organization I'm working for, and the others.

Those two kinds of animals are completely different. So obviously I have to put them in two different tables.

Now, it may be a stupid question, because I lack experience and knowledge, and actually I hope it is a stupid question with a very straightforward answer, but... COULD SOMEONE (please!) TELL ME how to set up a many-to-many relationship when one of the manies include more than one table ? Without having problems making the join when I access the data ?

Thanks and Cheers!

Hillel.

Sorry, my original message is way too vague.

Here's the understandable version (I hope)... Using mySQL, I'm building a fairly complex database, of which the most important table is an "articles" table made of the following columns : id, title, language, submitter, date and time of submission, date of publication, publisher, comments, of course the article's contents, and some more.

I also plan on creating a lookup table linking the "articles" table (in a many to many relationship) to an "authors" table that still does not exist.

I have two kinds of authors (organization's members/non members) that are TOTALLY different. The information about member authors is actually nothing more than their members profiles. Other authors' info includes first and last name, a possible email address, and comments.

So there are two different "authors" tables, and one of them is the "members" table (I'll have to create one for the non-members).

And I need to link the "articles" table to these authors. Some articles have more than one author, and some authors have written more than one article.

So it's a many-to-many relationship. I know how to create that when the problem is linking two tables : a lookup table. Two columns : authorID and articleID with the combination of the two being the primary key.

But I don't know how to link my article table with TWO authors table in an elegant, orthodox way. I came up with half-a-dozen solutions that are all pretty ugly. And problematic when I'll have to retrieve data (remember I'm not an expert, those might be imaginary problems) : I'll need to include, on php-generated pages that describe articles, a column with the name of the author (whatever his kind) and a link to it's "profile" page (also php-generated of course.)

I hope this request is more or less understandable. Thanks for your help!

Hillel.

I can think of 3-4 different things you could do, there are probably more.

First you could combine all the authors in one table and give each a type, this is probably not the best method as there is probably a lot of information that will be collected on the inhouse authors that won't be collected on the external authors.

Second you could have 3 tables. Author - inhouse - external where the author table just held the authorId and the information that would be common to both types (e-mail, profile, name etc. and "type") Quite possibly this would only need to be 2 tables as everything you collect on the external authors might be common. If someone is external there would be no record in the second table with their AuthorId.

Third you can have two totally seperate physical tables but you can combine them into a virtual table when doing query's. Slightly more complex coding and can be a resource hog if the two tables are very large. This is possibly the most elegant solution.

4th you can search both tables independently, possibly only doing the second search if the author wasn't present in the first.

and one more kind of ugly but simple to implement, add a 3rd field to the lookup table with a flag that defines which pool the author is in.

I can actually think of a couple more but one of these ought to fix you up.

Thanks for your help. I think I'll separate completely the "authors" table from the "members" table, and put a "trilean" (?) flag into the authors table : "is/was/isn't a member" This will lead to redundancies, and implies some efforts to avoid update anomalies. But it might be an advantage to create a distinction between member's profiles and their profile a authors : they might not want to use the same descriptions and email addresses as group members and authors.

Still, I believe I will probably face this problem again later. I appreciate your suggestions and keep working on it. Studying your suggestions made me think of this : is there a way to create such a table (where relevant_table is the table containing the relevant info) :

AUTHORS TABLE
id (int)
relevant_table_name (varchar(100)???)
id_in_relevant_table (int)

and then to make a query that would be able to use those table names to link to them, and then merge in a signle column of the output info of the same type but gathered in different tables???

Thanks again for your help. Cheers,

Hillel.