Member Avatar for newtojsp

I currently have a table structure like this

create table main(id int(10) unsigned NOT NULL AUTO_INCREMENT, fname varchar(25) NOT NULL DEFAULT '', `uid` int(10) unsigned NOT NULL,  PRIMARY KEY (`id`))

insert into main(1, 'abcd', 1), (2,'doll',1), (3,'animals',1)

CREATE TABLE category (
  `cid` int(3) NOT NULL AUTO_INCREMENT,
  `id` int(2) NOT NULL DEFAULT '0',
  `category` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`cid`)
) 

insert into category(1,1,'abcd1'), (2,2,'doll1')

Table category is related to table main. main=>category

I need 3 level nesting

main=>category=>subcat1=>subcat2

How do i modify the table structure?

Thanks

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)
  • 2 Contributors
  • 1 Reply
  • 63 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by baki100
Member Avatar for baki100

this is a bad example, instead you should have a look at composite entities then.
in short hand notation a better design for you above example would be:
main(id,fname,uid,cid)
category(cid,category)

underlined attributes are the pk and italics fk

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.