Page 1 of 1

database design

Posted: Tue Sep 18, 2007 12:26 pm
by shivam0101
i want to design a database.

It will contain all the art related things (like paintings, poetry, story, creative design).

The users will post their works. Admin will approve their works and it will be displayed. If someone who is interested will buy.

This is the structure i started

Tables
category
sub_category
work

category
cat_id, cat_name, cat_desc


sub_category
sub_cat_id, cat_id, sub_cat_name, sub_cat_desc

work
work_id, cat_it, sub_cat_id, work_name, work_desc


Is the structure correct? I also want to know, some works related to painting, drawing, etc will have images but, some works will not have. How to handle it? should i have to make separate tables for it?

Posted: Tue Sep 18, 2007 1:00 pm
by Begby
For the works that don't have images, you can probably get by by leaving the image field null.

As for the design it looks good except for the categories. You do not need both subcat and cat in the works table, you could just have the subcat and find the main cat with a join.

Posted: Tue Sep 18, 2007 1:46 pm
by josa
You do not need both subcat and cat in the works table, you could just have the subcat and find the main cat with a join.
Only if a work item must belong to a sub category.

You mentioned you want a way for admin to approve a work before it's published. I imagine you need som sort of 'approved' column in the work table. Or is the non approved work stored somewhere else?

/josa

Re: database design

Posted: Tue Sep 18, 2007 9:30 pm
by DrTom
shivam0101 wrote:i want to design a database.

It will contain all the art related things (like paintings, poetry, story, creative design).

The users will post their works. Admin will approve their works and it will be displayed. If someone who is interested will buy.

This is the structure i started

Tables
category
sub_category
work

category
cat_id, cat_name, cat_desc


sub_category
sub_cat_id, cat_id, sub_cat_name, sub_cat_desc

work
work_id, cat_it, sub_cat_id, work_name, work_desc


Is the structure correct? I also want to know, some works related to painting, drawing, etc will have images but, some works will not have. How to handle it? should i have to make separate tables for it?
I'd probably drop sub_category and do
category
cat_id, cat_name, cat_desc, parent_id

For top level categories parent_id can be NULL and you can grab sub categories like that

Posted: Wed Sep 19, 2007 3:49 am
by CoderGoblin
For categories you may want to look at this link Storing Hierarchical Data in a Database It contains two methods of storing tree level structures. The Adjacency List Model and Modified Preorder Tree Traversal. The second is my preferred method. Using this model the number of levels in your category list does not matter. Although you only have two levels at the moment you will probably be suprised at a later date when the requirement changes.