database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
shivam0101
Forum Contributor
Posts: 197
Joined: Sat Jun 09, 2007 12:09 am

database design

Post 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?
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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
DrTom
Forum Commoner
Posts: 60
Joined: Wed Aug 02, 2006 8:40 am
Location: Las Vegas

Re: database design

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
Post Reply