Database design

Question

0.00/5 (No votes)

See more:

I need help in designing a database for a yellow page likewise website. The following are the basic requirements.

1) There would be approx four levels in which categories will fall.

2) Child categories can fall in more than one parent category and at different level.

3) All categories would be distinct means there would not be any duplication of record.

The below is the table structure for category which I developed but problem is when linking the child category with more than one parent category and at different level.

Table CATEGORY
[CATEGORY_ID],[CATEGORY_NAME],[IS_ACTIVE],[CATEGORY_LEVEL],[REF_CATEGORY_ID].

I would appreciate help from your busy schedules. Thanks in advance.

Regards,

Amit

Posted 6-Jul-10 1:33am

amitjind

Updated 6-Jul-10 2:19am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pavel Yermalovich · Answer 1 · 2010-07-06T01:43:00

Solution 1

Not so long ago I have designed the database for project. I used the following script and structure:

<br />
CREATE TABLE [dbo].[Categories](<br />
	[CategoryID] [int] IDENTITY(11,1) NOT NULL,<br />
	[Name] [nvarchar](80) COLLATE Cyrillic_General_CI_AS NOT NULL,<br />
	[ParentCategoryID] [int] NOT NULL,<br />
	[GameID] [int] NOT NULL,<br />
 CONSTRAINT [XPKCategories] PRIMARY KEY NONCLUSTERED <br />
(<br />
	[CategoryID] ASC<br />
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />
) ON [PRIMARY]<br />
<br />
GO<br />
USE [auction2]<br />
GO<br />
ALTER TABLE [dbo].[Categories]  WITH CHECK ADD  CONSTRAINT [R_39] FOREIGN KEY([GameID])<br />
REFERENCES [dbo].[Games] ([GameID])<br />

Posted 6-Jul-10 1:43am

Pavel Yermalovich

Updated 6-Jul-10 1:44am

v2

Comments

amitjind 6-Jul-10 8:07am

Hi Thanks for the quick reply. I did not follow second command for Alter. What is GameID how can I use it?

Pavel Yermalovich 6-Jul-10 8:11am

Don't look at GameID. This is just for my process

amitjind 6-Jul-10 8:25am

Thanks again I guessed it but thought it is for linking other table..anyway....few more questions.

1) Would it restrict duplication of record..I believe IGNORE_DUP_KEY = OFF will help..right?
2) How can I link category to more than one parent category?

Pavel Yermalovich 6-Jul-10 10:07am

In this case I would create ParentCategories table to implement MANY-TO-MANY connection where you will store categories ID and their paterents' category id