Hi this is my first post in this forum, I have one query regarding database design.

I have 3 tables.

1) Upload(UploadId Primary key)
2) Category(CategoryId Primary Key)
3) Subcategory(SubcategoryId primary key)

relation among in them is...

Category (1) : (Many) Upload

Category(1) : (Many) Subcategory

My question is how should I design database and foreign key contraints?

I am eagerly waiting for your answers.

Thnx.

you don't need separate category and subcategory tables

this might help: Categories and Subcategories

Thank You r937,

I didn't know this technique before.

Much Appreciated.

Instead of adjacency model recommanded by r937,
can i write like following?

tblUpload
-----------------
UploadId(PK)
.................................................. .....................................

tblCategory
-----------
CategoryId(PK)
UploadId(FK)
.................................................. .....................................

tblSubCategory
---------------
SubcategoryId(PK)
CategoryId(FK)
UploadId(FK)
.................................................. .....................................

Why does UploadId appear in the Category table? It looks like you can determine the Category from the SubCategory table anyway. So I'd expect you can remove UploadId from Category. Of course, I don't know your data.

The adjacency model lets you have subcategories of subcategories, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those, and subcategories of those (etc)

Yours allows only two layers deep. Is this what you require?

pootle flump ur codings are working excelent.

Yes I want only 2 levels deep.

CORRECTED ONE:->

tblUpload
-----------------
UploadId(PK)
.................................................. .....................................

tblCategory
-----------
CategoryId(PK)
UploadId(FK)
.................................................. .....................................

tblSubCategory
---------------
SubcategoryId(PK)
CategoryId(FK)
.................................................. .....................................

This doesn't look right either. If it is correct then each CategoryId can only have ONE upload, which appears to contradict what you said before: "Category (1) : (Many) Upload". My guess is that either CategoryId or SubCategoryId actually belongs in the Upload table and that UploadId should not go in the Category table.

Unfortunately these kinds of design-by-forum exercises tend to make very slow progress and are rarely much help in my experience. The best option may be to hire someone to assist you if you aren't familiar with how to model a database.

Modelling a substantial business problem, yes. Something like this - nah. This and other forums have helped sort out thousands and thousands of problems of this sort. If they aren't of any use then why do you continue to contribute?

Kalundra - the principle problem is you have not explained in detail what you are trying to achieve, what you are modelling, what are the real world circumstances. You keep posting models, but we don't know if they are appropriate or not unless you tell us the background.

pootle flump ur codings are working excelent.

I am developing community based website where people can contribute their images, documents, audios and videos.
client's requirement specification tells that all of those uploads have categories and subcategories.(no subsubcategories). After thinking lots of alternate solutions I have come across following design..

Upload belong to particular category or subcategory. Categories have subcategories or they don't have any subcategory. In that case it may be NULL.

Category
------------
CategoryId(PK)
CategoryName

Subcategory
------------
SubcategoryId(PK)
CategoryId(FK) ref Category(CategoryId)
SubcategoryName

Upload
-------------
UploadId(PK)
CategoryId(FK) ref Category (CategoryId)
SubcategoryId(FK) ref Subcategory (SubcategoryId)
UploadName
....

really want to know your point of view..
===========================================

Category
------------
CategoryId(PK)
CategoryName
ParentCat(FK) references Category (CategoryId)

Upload
-------------
UploadId(PK)
UploadName
CategoryId(FK) references Category (CategoryId)


K.I.S.S.