The usual mechanism for this is what’s known as a ‘join table’, in this case “topic_membership” might be a decent name for it.
The join table should have a foreign key to topics, a foreign key to subjects, and possibly a primary key of its own, (probably an autosequenced one.) Nothing else is required.
A simple scan of this table is all that’s necessary to list what topics are contained in the Europe subject (once you have europe’s subject ID) or what subjects a topic belongs to. It can be joined directly to the articles table to get a full listing of all the articles under the ‘mathematics’ subject (along with a join to topics if you wish to list the topic name of each article.)
The standard way of normalizing a many-to-many relationship is to put an association table between them.
For example. if you have a scheduling database. You will have a table of workers. you will also have a table of workable shifts. Each workers will work more than one shift, and each shift will have more than one worker. So you you create a assocition table between them that takes one worker and one shift per entry and call it work_assignment or something.
In large part, the design of your DB depends on what sort of information you want to get out of it. I’m guessing that you want to start with a list of subject headings, and generate a list of articles (possibly broken down by topic) for that subject.
Like chrisk said, an intermediate table is the way to go here. Here are your basic tables:
In each, the %tablename%_ID field is a generated primary key. Now, admittedly, I’m not the most DB-savvy guy in the world, so you may want to wait for someone with more expertise to comment on this–there may be potential pitfalls based on the sort of queries you’re writing, or even on how MySQL is implemented.
Near as I can tell, folks have all got it right. I started learning databases without any help at all, and the many-to-many relationship concept was an absolute bear for me to figure out; when I finally thought of putting a third table between the two tables I wanted to join, I did a little dance of joy. Then I found out this was common knowledge to database designers :).
My favorite book on database design is Database Design for Mere Mortals. It clarified a lot of issues for me, and deals with issues in the conceptual stage rather than getting bogged down in the details of a particular program. It’s fantastic.
I create a table called for example TOPIC_MEMBERSHIP
it has the following fields:
ID, TOPIC_ID, SUBJECT_ID
where ID is the table PK and the other 2 contain the identifying, unique number for the TOPIC and SUBJECT
Therefore it could look like this
ID, TOPIC_ID, SUBJECT_ID
1, 7, 18
2, 7, 9
Where 7 = a row in the TOPIC table about ‘Highest Mountains’ and 18 = a row in the SUBJECT table about ‘Geography’?
Where 7 = a row in the TOPIC table about ‘Highest Mountains’ and 9 = a row in the SUBJECT table about ‘Europe’?
Yes, that looks very good so far… you’d have other records in your topic_membership table of course, for other topics.
Inner and outer joins… this took me the longest time to sort out myself. My first piece of advice is to stick with inner joins until you ask a question for which the answer is ‘use an outer join’… on that day things will become clearer.
As I understand it, the main use of an outer join is to find records in one table that have no corresponding records in another table. In your database, you could use an outer join to find topics for which there are no articles.
Probably not… but it depends on the kind of interface (if any) you’re using to populate the rest of the database.
Since you say you’re working with PHP, I imagine it’s possible to create an admin interface to the database that way. I’d be thinking in terms of an ‘add new topic’ and ‘modify topic’ form which would include either a one listbox or two listbox interface for picking the subjects that topic belongs to… let’s say a two listbox style, for the sake of argument… one of those arrangements where you select and entry and click a button to move it from one box to another. (The box on the left would probably list the subjects that the topic does not relate to, and the box on the right lists the subjects that it does, so you can indicate any possible change by moving back and forth.)
Then you go through and add each topic membership based on the entries in the right box. (Deleting old memberships that might have been deleted first.) You can have a similar list of topics underneath each subject’s modify page if you want too.