CODEWITHSUNDEEP
phpmysqlsql
user3177012
user3177012

Reputation: 691

MySQL Categories List vs Sepperate Categories Table

I'm creating a blogging website which lets you assign a category to a blog. The original table that I had looked like this:

+----+----------+-------+---------+
| ID | CATEGORY | TITLE | ARTICLE |
+----+----------+-------+---------+
| 01 | cars     | Title | Article |
| 02 | cars     | Title | Article |
| 03 | sport    | Title | Article |
| 04 | music    | Title | Article |
| 05 | music    | Title | Article |
+----+----------+-------+---------+

So as you can see, the category is a written attribute. The subject came up of having a sepperate categories list which I created to look like:

+-------------------+
| CAT_ID | CATEGORY |
+--    --+----------+
| 01     | cars     |
| 02     | sport    |
| 03     | music    |
+--------+----------+

Which alters the original table to:

+----+--------------------+----------+-------+---------+-----------+
| ID | BELONGS_TO_BLOG_ID | CATEGORY | TITLE | ARTICLE | AUTHOR_ID |
+----+--------------------+----------+-------+---------+-----------+
| 01 | 01                 | 01       | Title | Article | 01        |
| 02 | 01                 | 01       | Title | Article | 01        |
| 03 | 02                 | 02       | Title | Article | 02        |
| 04 | 02                 | 03       | Title | Article | 03        |
| 05 | 02                 | 03       | Title | Article | 03        |
+----+--------------------+----------+-------+---------+-----------+

Now if I wanted to delete cars which is category 01, rather than delete it (because obviosly 2 articles are in this category) I would probably change this to unnassigned. So no problems here but if I was then to delete music (03) and change this to unnassigned I would end up with ID's 01 & 03 being unnassigned. So now when I run searhes for unnassigned articles, which should return 4, I am only going to see 2 or an error.

Wouldn't it be better to have the original table and when someone changes category cars they simply UPDATE the record?

Upvotes: 2

Views: 66

Answers (1)

sumit
sumit

Reputation: 15464

Well, you can validate it . If you need to follow the database rules definitely you have to check parent-child relationship between 2 tables. There are 3 ways

1. FOREIGN KEY : This will prevent parent deletion , until child record is there

ALTER TABLE items
ADD FOREIGN KEY (category)
REFERENCES category(categoryTable)

2. Validation from php script :Some storage engine may not support point 1. In that case you can simply validate using php. Before deleting category just count if it has some product are not

$sql="select count(*) as cnt  from items where category=CATEGORY_TO_BE_DELETED";

if(mysql_query('cnt')>0) //means it has some items 
  //do not delete or throw some error
else
  //delete

3 Logical deletion/soft delete: Sometimes you need to delete category (suppose you have 1000 products under it and you can't delete it). Then you can have some flags like is_deleted. You can on or off that flag to delete undelete it

Upvotes: 2

Related Questions