Database Models

[CMCDragonkai]

Hello, my name I is Roger,

 

I am new to using mysql databases extensively, however I have experience with PHP and Mysql.

 

The problem I have, is something that I just can't seem to find any information on.

 

Basically, I have to make a database for a supplier.

 

They have a huge range of products all in different kinds of categories and such.

 

An example would be like:

 

Women's shirts/T shirts/Pants/Sweatshirts etc

Kid's toys/Jumpers/socks

Men's underwear/hats

 

And many many more.

 

Some products have certain attributes like different ethnic designs, which appeals to different people.

 

I am stumped as to how I am supposed to make such a database.

 

Do I start with one big table with many columns stating attributes, or do make different tables all having different types of categories. However that results in many duplication. Since many products are in more than one category. Especially when I add in the attributes of products.

I first thought of a hierarchical model, because that made sense. But I know that is not very standardized. When I learned about the many to many model, I still need an example of how someone else did it.

 

So basically what I'm asking, is if someone can provide me a roadmap as to how they did it when they had many types of products and had to make them a usable database so it could searched from a single line of text, just like google.

 

Thanks for any help.

 

Roger.

[ignace]

would it be possible you need something like?

 

Men

+ shirts

  + sweatshirts

+ socks

  + ..

Women

+ shirts

  + sweatshirts

+ socks

  + ..

Kids

+ toy's

  + < 5 year

  + > 5 year

 

CREATE TABLE category (
  id int(11) NOT NULL auto_increment,
  parentId int(11) NOT NULL default '0',
  ...
  PRIMARY KEY (id)
);

 

for the products problem as many products fit into many categories

 

CREATE TABLE product (
  id int(11) NOT NULL auto_increment,
  ..
  PRIMARY KEY (id)
);

CREATE TABLE product_to_category (
  productId int(11) NOT NULL default '0',
  categoryId int(11) NOT NULL default '0'
);

[CMCDragonkai]

So you're saying to create as many tables that fit into main categories.

[ignace]

no, wait here's an example:

 

INSERT INTO category (id, parentId, name, ..) VALUES
-- our main categories (ancestors)
(1, 0, 'Women', ..),
(2, 0, 'Men', ..),
(3, 0, 'Kids', ..),
-- subcategories for women (notice the 1 for parentId, which refers to row 1)
(4, 1, 'Shirts', ..),
(5, 1, 'Other', ..),
..
-- subcategories for men (notice the 2 for parentId, which refers to row 2)
(10, 2, 'Sweatshirts', ..),
(11, 2, 'Other', ..),
...
-- subcategories for kids
...

INSERT INTO product (id, name, ..) VALUES
(1, 'A sweatshirt'),
(2, 'A shirt'),
(3, 'A toy'),
(4, 'Another thingy'),
...

INSERT INTO product_to_category (productId, categoryId) VALUES
(1, 10), -- 'A sweatshirt' is now categorized under 'Sweatshirts' which is a subcategory of 'Men'
(2, 4), -- 'A shirt' is now categorized under 'Shirts' which is a subcategory of 'Women'
...

 

using a self-join you can get the parent and his children

 

example self-join:

 

SELECT * FROM table1 p INNER JOIN table1 c ON p.id = c.parent_id WHERE p.id = ? ORDER BY c.parent_id DESC

 

you may pm me, if you require some more explanation, we then may workout a working example

[ignace]

sorry little typo, should be:

 

SELECT * FROM table1 p INNER JOIN table1 c ON p.id = c.parentId WHERE p.id = ? ORDER BY c.parent_id DESC

[CMCDragonkai]

Oh so you're going into that hierarchical relation ship model right?

 

I understand how, you're using main categories,

 

Then going into sub categories.

 

Then what you have done is made it possible to enter values into them.

 

What I'm confused about,

 

Is which ones are the actual tables, and which one is data.