Page 2 of 2

Re: Store data + advice

Posted: Fri Dec 30, 2005 10:19 am
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!

Posted: Fri Dec 30, 2005 11:32 am
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

Posted: Sat Dec 31, 2005 8:33 am
by BDKR
jshpro2 wrote: the devnet's greatest hits album
Where can I get this?! :D

Posted: Tue Jan 03, 2006 5:38 pm
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