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.