DATABASE TABLE COMMENT'S OR SUGGESTIONS

[wmabear54]

Hello guy's and gal's

I am to the point in the project when I am ready to create the final tables for the database excluding more tables for a shopping cart, also I have not included all the fields into the forth table for images. I would appreciate any comment's on my design and structure, and the key's.


first table is for the website to work with.
Master_url

Id int not null primary key auto_increment,
url varchar (100),
title varchar (100),
description text,
keywords text,
body text

second table is for the major catagories
master_cat_url

id int not null primary key auto_increment,
master_id int not null,
url varchar (100),
title varchar (100),
description text,
keywords text,
body test

third table is for the subcatagories
master_cat_subcat_url

id int not null primary key auto_increment,
master_id int not null,
cat_id int not null,
url varchar (100),
title varchar (100),
description text,
keywords text,
body text

forth table is for the items for sale
master_cat_subcat_items_url

id int not null primary key auto_increment,
master_id int not null,
cat_id int not null,
subcat_id int not null,
url varchar (100),
title varchar (100),
description text,
keywords text
body text

Have I got it all wrong or does it look ok

Thanks in advance

Mike

[cristiu]

A more good solution is to merge the second table with third tabel(major categories with subcategories) like this:
categories:
id int not null primary key auto_increment,
master_id int not null,
parent_id int not null
url varchar (100),
title varchar (100),
description text,
keywords text,
body text

where parent_id is -1 for major categories and for subcategories is id of which parent belong...
I this mode you may have unlimited subcategs...
If I was no so explicit please tell me and I will explain you with examples...

[wmabear54]

Hi cristiu

Thanks for the input. I am getting the feel for what you are talking but it is not clear yet, if you would please show me example,

Mike

[php~pro]

Its a good idea. Similar to the way a threadind comments or bb board database is structured. It should reduce query times though these are second fractional anyway, but if like me you check data via an sql administrator to sped up the process you can also see exactly what is what. No flicking between tables.

[cristiu]

We supose have two major categories: maj_categ1 and maj_categ2 and supose that we have three subactegories one belongs to maj_categ1 and two belongs to maj_categ2;
-maj_categ1
--subcateg1
-maj_categ2
--subcateg2
--subcateg3
So in table we will have:
for maj_categ1 record: id=1,parent_id=-1,masted_id=........
for maj_categ2 record:id=2,parent_id=-1,master_id=.......
for subcateg1 record:id=3,parent_id=1,....
for subcateg1 record:id=4,parent_id=2,....
for subcateg1 record:id=5,parent_id=2,....

So, did you understand?

[wmabear54]

Thanks cristiu for the info, however I think I will try my way first as I have a better understanding of it and have worked out methods in my head of how to use it.

php~pro thanks for the offer to check it out using sql administrator I will PM you at a later date after I get more details worked out.

Thanks guys
Mike