Store data + advice

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Jeroen Oosterlaar
Forum Commoner
Posts: 37
Joined: Sun Nov 06, 2005 4:12 pm

Re: Store data + advice

Post by Jeroen Oosterlaar »

BDKR wrote:Where is the redundancy coming from? You can use one table to store a heirachal structure of infinite depth without redundancy. If there is something I'm missing here, please explain.
Okay then, let's look at only two levels of my model suggestion to keep things simple:

Code: Select all

MainCategory { _mctId_, mctName, ... };

SubCategory { _sctId_, sctMainCategoryId_, sctName, ... };
It is obvious that the entity SubCategory depends on MainCategory: a SubCategory belongs to a MainCategory and a single MainCategory can have one or more SubCategories. Now, suppose the following two tables:

Code: Select all

MainCategory
--------------------------------------------------------------------
  mctId     |  mctName               
--------------------------------------------------------------------
  1         |  Books
  2         |  Music
  3         |  Cars
--------------------------------------------------------------------

SubCategory
--------------------------------------------------------------------
  sctId     |  sctName                   |  sctMainCategoryId
--------------------------------------------------------------------
  1         |  Horror                    |  1
  2         |  Romantic                  |  1
  3         |  Rock                      |  2
  4         |  Pop                       |  2
  5         |  European                  |  3
  6         |  American                  |  3
--------------------------------------------------------------------
Now, following your idea of using a single table, I imagine the following table (Category) based on the two tables I presented above.

Code: Select all

Category
--------------------------------------------------------------------
  catId     |  catSubName                |  catMainName
--------------------------------------------------------------------
  1         |  Horror                    |  Books
  2         |  Romantic                  |  Books
  3         |  Rock                      |  Music
  4         |  Pop                       |  Music
  5         |  European                  |  Cars
  6         |  American                  |  Cars
--------------------------------------------------------------------
Hmmm :lol: ! Looking back at my own examples I have to admit that you are right. Because, what is the difference between the number 1 and the category name Books? Although, personally, I prefer separated tables, I must admit that using a single table does not implicate redundancy.

But only under the condition that the main category entity does not have further specification of its own!
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

BDKR,
I was talking about a 200mb "products" table


Jeroen Oosterlaar,
That is almost what we're talking about, let's look at this for example

Code: Select all

id     |     name      |    parent_id     
---------------------------------------------
     1      |    books      |      0
     2      |   fiction     |      1
     3      |   non-ficton  |      1
     4      |      CDS      |      0
     5      |    hip-hop    |      4
as you can see each root node is identified by a parent_id of 0, using this an entire branch of your category tree can be moved onto another node. Let's say you have a new category called christmas gifts, and you want CDS to be under christmas gifts, you add the new category "christmas gifts" with a parent_id of 0, then you change CD's parent_id to that of the christmas gifts category, you have now just moved CDs into the xmas gifts category along with every sub-category infinently below that, and every product there-within.


and then your products table just looks like this:

Code: Select all

id    |     name                         |     parent_id     
--------------------------------------------------------------------
     1      |    devnet's greatest hits CD      |      4
in this example the devnet's greatest hits album is under the category "CDs", it could just as easily be moved to an arbitrary sub-category with something as simple as

Code: Select all

update products set parent_id = 5 where id = 1 limit 1
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

jshpro2 wrote: the devnet's greatest hits album
Where can I get this?! :D
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

There seems to be some controversy whether 1 or 3 tables are used

Here is the table structures I have at the moment .. 4 tables

Code: Select all

CREATE TABLE maincat (
  mainid int(25) NOT NULL auto_increment,
  catname varchar(100) NOT NULL default '',
  PRIMARY KEY (mainid)
) TYPE=MyISAM;

CREATE TABLE subcat (
  subid int(25) NOT NULL auto_increment,
  mainid int(25) NOT NULL default '',
  name varchar(100) NOT NULL default '',
  PRIMARY KEY (subid)
) TYPE=MyISAM;

CREATE TABLE items (
  itemid int(25) NOT NULL auto_increment,
  subid int(25) NOT NULL default '',
  name varchar(100) NOT NULL default '',
  code varchar(55) NOT NULL default '',
  price decimal(11,2) NOT NULL default '0',
  stock varchar(100) NOT NULL default '',
  description text NOT NULL default '',
  PRIMARY KEY (itemid)
) TYPE=MyISAM;

CREATE TABLE itemimages (
  photoid int(5) unsigned NOT NULL auto_increment,
  itemid int(5) unsigned NOT NULL default '0',
  photo_filename varchar(25) default NULL,
  PRIMARY KEY (photoid)
) TYPE=MyISAM;
My menu system is poulated from maincat & subcat tables

Is that a fair structure?


Thanks
Post Reply