Self relating or one to meny mapping

Will a self relating table handle this hirarcal eventuality or do I need a one to meny mapping instead.
Thanks
Id	Name		Parent
1	Clothing	                     1
3	Shoes		3
7	Accessories	                     7
18	Evening		3
18	Evening		1
74	Bags		7
18	Evening		74
7	Accessories	                     1
7	Accessories	                     3

Open in new window

LVL 4
ruffoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
why do you have 3 Accesorries with same ID=7 & with different parents ?!?...
data looks a bit strange to me...
0
ruffoneAuthor Commented:
"7     Accessories    7" Sits at the top of the hierarchy
"7     Accessories    1" though Accessory, is a child of "Clothing"
"7     Accessories    3" though Accessory, is a child of "Shoes"

 
0
dportasCommented:
What is the key of this table? It looks like it must be (Id, Parent). But based on your sample data it looks like the Id also determines the Name, which is therefore a non-key dependency.

If I'm right then I would suggest two tables instead:

CREATE TABLE t1
 (Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(20) NOT NULL);

CREATE TABLE t2
 (Id INT NOT NULL REFERENCES t1 (Id),
  ParentId INT NOT NULL REFERENCES t1 (Id),
 PRIMARY KEY (Id,ParentId));
 
 

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ruffoneAuthor Commented:
Thanks dportas. This is kind of what I did. except I do not have an id field for the t2 equivalent. It seem to be working quite well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.