Tables Design Question

[dcotter]

Okay...I need to design a database according to the following: There will be a list of categories, and a number of listings. Each listing will belong to one or more category. So when you go to that category webpage, all the listings for that category are displayed.

What I have now is a categories table and a listings table, with a category_id field in the listings table that relates back. This is making it difficult for one listing to belong to numerous categories. I've looked into the SET() data type, but I think that is not a good idea. The only thing I can come up with is adding the listing to the listings table twice, once for each category, but that brings up other issues I don't want to have to deal with.

Someone has to have done something like this in the past...can anyone provide some insight? I should mention that I'm using MySQL =)

[[deleted]]

What you want to do reeks of a many-to-many relationship.

Create two tables: categories(id,name) and listings(id,name)
then create a new table called links(id,categoriesid,listingsid)

Every time you want to link a category to a listing, you make a new entry in the links table with the proper ids in it. Simple eh?

read more here:
http://www.phpbuilder.com/columns/barry20000731.php3

[dcotter]

Vincent...funny thing, right after I posted I read that same article...great article. That's sorta the way I was leaning already...but that brings up tough issues for me.

1) When adding a new listing...I want to be able to select multiple categories via a multiple select box. This would then entail multiple queries (one to add to the listings table, one for each category selected into the third table).

2) The REAL sticky issue comes when I want to EDIT a listing. I would need to first retrieve all the categories the listing is in from the third table...but say I want to EDIT that listing from categories 1 and 4, to categories 2, 3, and 6. That's 5 queries, two to delete the previous, and 3 more to add.

Am I making this more complicated than it is, or is this the way it'll need to be done?

Thanks Vincent!

[Anon]

Vincent,
The link table structure in the article is flawed. The initial id field is a waste of space and prevents the automatic protection against repeated entries that you'd get from using the logical primary key (in this case, categoriesid, listingsid).

• Beth

[[deleted]]

That is good to know.

Actually I just refered to the article to give Daren the basics of normalization.
Did you contact the author of the article about this so he/she can correct it?

[[deleted]]

1) Yes and no. Yes it would take multiple queries, but you can combine several into one. You can add several records in a table in one query.

2) Same thing, you can delete all old entries in one query and insert the new ones in one query.

This is the only drawback of normalization, you have more tables, so inserting and updating takes more queries.
On the other hand, selecting and searching is much easier and it does make things one hell of a lot easier to maintain and to understand.
For example, if you changed the name of a category, with normalization you just update the record of that category in the categories table, and immediately the category name has changed for every listing that is linked to it.

[smarlowe]

And don't forget that good indexing will make a big difference in the performance of a many to many join table setup.

[paulken]

It is flawed. It can be worked around by creating an unique index for the two fields, and then we're back in business.

I can't right now find a good argument for keeping the ID field, though🙂