help with normalized table please

[james438]

I am still pretty weak on creating normalized tables and seldom create them, but I am trying to create one where there is a table for articles and each article can have several categories. I figure the tables should look something like the following:


Table_1

ID, article_content, category_ID


Table_2

ID, category_name, category_ID

I just don't see how to tie them together. I strongly suspect that the tables should look different as well.

[djr33]

I would recommend the following:

TABLE `articles`
`ID`, `content`, [...]

TABLE `art_cat_relationships`
`art_ID`, `cat_ID`

TABLE `categories`
`ID`, [...]

The method in your post assigns articles to categories twice, once in the articles table and once in the categories to articles table.

My method suggests using a table for categories and another for articles. Then a third will just store relationships between them.
If you have an article in a single category you will have one entry for the article, one entry for the category (maybe borrowed for other articles as well), and one entry for the relationship table.

Alternatively you can have a "primary" category determined in the article table itself (as in your example) then have a secondary listing in the relationships table for one or more secondary categories. That's not entirely standardized (since the same information is being stored in two places), but it might be slightly more efficient if many articles will belong to exactly one category.

[james438]

so for TABLE `art_cat_relationships` where there is an article with three categories we would have data of


`art_ID`, `cat_ID`
1 1
1 2
1 3

correct?

[djr33]

Yes, just like that. That way you have an independent table just for linking. Of course you could make it more complicated such as giving each article a different title when it is in a different category-- there would be a title field added to `art_cat_relationships`.


Generally, what you want to do is create one table for each type of thing. In your system, you have three types of things: articles, categories, and relationships. Give each an ID and then you can link them together in any way you'd like.


(Of course you probably want to name these things a little more simply, but for the moment those make it clear.)

[james438]

Thanks for your help. Much appreciated :)

[djr33]

By the way, I use a simple rule when to determine if I need to create a new table: cells in a table can hold a single piece of information. If I ever need to store more than one piece of information in an individual cell, then I know it's time to create a new table for just this cell: and I'll link it to the items in the current table using an ID, like the article_ID above.

(There is an exception to this: if you are storing a list of information in a cell, then that's fine. But realize it won't be available for any normal sorting/searching operations. It exists as a string and only a string, regardless of whether technically it is a "list".)

[james438]

That is a useful tip.

I finally got around to creating my normalized table. The php involved was rather complicated for me though.

I have a new problem though. For security my hidden articles are still not visible, but under certain circumstances the titles are. Here is the query I am using to keep the articles with certain tags from displaying.


SELECT article.title, article.ID
FROM article, art_cat_relationships
WHERE art_cat_relationships.cat_ID=13
AND art_cat_relationships.art_ID=article.ID
AND art_cat_relationships.cat_ID!=30
AND art_cat_relationships.cat_ID!=29
ORDER BY title

It makes sense to me why it does not work, but I am having difficulty figuring out how to keep the hidden files from being displayed as well. I could generate an array of the hidden files with tags that should not be displayed and somehow incorporate that into the query, but it seems to me that there should be a simpler way of doing this with MySQL.

[djr33]

Interesting. That's a bit beyond my experience with MySQL. One option is to use PHP instead: find all non-hidden IDs and go through each result (a standard while mysql_fetch... loop) and then do a second query that will look at what you're actually now searching for. It's indirect but should work.

[traq]

if it's not "too late in the game," I would suggest adding another column, maybe in the relationships table, that defines the "hidden" status of the article (public, hidden, title-only, etc.). I don't think it's generally a good idea to put specific exceptions in a query - that sort of logic belongs elsewhere. If you will ever get more than those few exceptions, it could become a nightmare to maintain, as well.

[james438]

djr33, it looks like we were thinking along the same lines, because before reading your response I ended up doing what you are suggesting :). It seemed simple enough and didn't require much coding either nor does it look to be processor heavy at all.

Basically I do the standard mysql_fetch_array loop as usual and the first thing it does is compare the art_cat_relationships.art_ID with a list of art_IDs that are hidden. If there is a match then continue.

Thanks for explaining traq. Your idea sounds good too, but since I already have djr33's method in place I'll use that.

It appears that I am unable to mark this thread as resolved. Would you be able to do that djr33?

[kuau]

It seems to me you guys (Daniel & James, who have both helped me immensely with php over the years [thank you!]) are making this WAY more complicated than it needs to be. So, hopefully, I can finally help you with something. I may not be understanding exactly what you are trying to do, but this is how I would do it...

I simplified your table name to make it easier to follow. This is your code...


SELECT article.title, article.ID
FROM article, artcat
WHERE artcat.cat_ID=13
AND artcat.art_ID=article.ID
AND artcat.cat_ID!=30
AND artcat.cat_ID!=29
ORDER BY title

First, I agree with Traq that you need another column... but in the article table, not the artcat table.... a simple 1/0 field that indicates whether the article should be visible or not.


SELECT * FROM articles WHERE art_id IN(SELECT art_id FROM artcat WHERE cat_id = 13) AND `active` = 1 ORDER BY `title`;

or, without the `active` column...


SELECT * FROM articles WHERE art_id IN(SELECT art_id FROM artcat WHERE cat_id = 13) AND `art_id` NOT IN(29,30, 47,etc) ORDER BY `title`;

There you have your data with no php filters required. No muss, no fuss. :)