I am trying to build a basic MySQL based database for a simple php based web site.
have been reading a few articles on relational modelling, and have the basics of SQL down, but need some help modelling.
Situation:
The site will have many ARTICLES, each written on a single TOPIC. The web site will have various SUBJECTS that may contain many TOPICS, however each TOPIC may belong to one or more SUBJECTS.
Eg the SUBJECT ‘Geography’ may contain various ARTICLES on many TOPICS such as ‘Capital Cities’, and ‘Highest Mountains’. The TOPIC ‘Highest Mountains’ may also belong to the SUBJECT ‘Europe’.
I am sure that this is a ‘simple’ issue of normalizing a many-to-many relationship, however I just need a kick in the right direction…
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’?
and
Where 7 = a row in the TOPIC table about ‘Highest Mountains’ and 9 = a row in the SUBJECT table about ‘Europe’?
Alright Chrisk you have beaten me this time. My DBA-fu is not as fast as yours. But one day we will meet again and I will be prepared for you with the speed of an E15K.
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.
Another question. How would I populate TOPIC_MEMBERSHIP? Just manually rationalise every Topic and Subject, with its primary key ID and then fill the table?
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.
