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?
database design
Moderator: General Moderators
Only if a work item must belong to a sub category.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.
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
I'd probably drop sub_category and doshivam0101 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?
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
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.