2 question about better optimize database design

Question

1) In mysql engines, MyISAM Is Better Or InnoDB ?

2) i want programming a cms that it could add a post with multi category how i must design my database to have a better performance with a few query ?

in php how can list posts which category for example = 1 ?

thank you

Answer 1

1) If you need foreign keys relations on DB level use InnoDB else use MyISAM

2) You can store all categories in one table with schema like that

create table categories (
Category_ID int NOT NULL,
ParentCategory_ID int NOT NULL default 0,
CategoryName varchar(150)
);

with Category_ID primary key

3)

$sql = select * from posts where category_id = 1;
mysql_query($sql);

edit : Schema of post table (example)

create table posts (
    Post_ID int NOT NULL,
    Category_IDs varchar(50) NOT NULL,
    POSTDescription varchar(1000),
    POSTTime int NOT NULL
)

Post_ID is primary key of posts table.

note the Category_IDs is now varchar store value of categories in it like 1,2,3 if your post belongs to cateory 1,2 and 3.. and for deleting all posts belonging to category 1 you will run following query

$sql = DELETE
FROM `posts`
WHERE FIND_IN_SET( '1', `Category_IDs` ) >0;