multiple databases...yes or no?

[dpabla]

I have a question in regards to using multiple databases I wanted some advice. I have a jewelry website I am working on and we are switching everything dynamically. Lets say we are selling Jewerly. The user clicks on Jewerly and from Jewerly they click on a brand, lets call it Brand A - under Brand A there are “Rings, Necklaces, Engagement Rings” When a user clicks on “Rings” there is a page that displays sub-categories like “Wedding Band” and so forth.

So each category like Rings, Necklaces, etc, have their own sub=categories of items. How would I go about creating the database? Should I have 1 DB per Vendor (i.e. Brand A, B, C, D)? I am stuck figuring this out because each Vendor A, B, C, D etc have their own MAIN CATEGORIES and in those MAIN CATEGORIES you have sub-categories. I also want to throw in that once a user lets say: Click Vendor A > Rings > Enagement > 14kt Gold 

That product page lets called it display.php?id=32  brings up the 14kt Gold - that page will display the ring and on the right hand side you have pull down menu for "Metal" "Matching Item" and "Collection" which the collection is all teh items listend under "Engagement Rings" >>>> Now what I just mentioned, EACH vendor wil have the same structure....So I am curious if I need seperate DB for each Vendor or 1 DB called JEWERLY and just create billions of these tables and each table = Engagement Ring, Necklace etc. I am confused because there are sub catageries so I dont know how I would be calling sub categories.

Thanks so much for your help.

[chronister]

Seems that dealing with multiple databases in your code may be a pain in the $%#, but that's just me.

I am not an expert with relational databases, but if I were doing this I would structure it like so:

table - products
  product_id | product_name | vendor_id | category_id | subcat_id | price | another_field | another_field | etc   

table - vendors
vendor_id | vendor_name | more_vendor_info | more_vendor_info | more_vendor_info | more_vendor_info |

table - category
category_id | category_name | category_descripton | etc.....

table - subcategory
subcat_id | subcat_name | subcat_description | etc

The product table contains the id's relating to the other tables. By sectioning off the data you want to store with tables and then referencing the id's in one main table (the products table here) you can save space and the amount of tables needed.

If I am way off base here, please someone else chime in and lemme know

Nate

[dpabla]

Nate: Thanks alot for your reply back...I was reading online that having multiple DB's isnt the way to go...so if I am understanding correctly:

User point of view:
Site visitor clicks on Vendor "Hearts on Fire" > under that, they are displayed what HOF sells which are:
Rings| Necklace | Bracelet

The user than proceeds to click on RINGS and that page brings up sub-categories for RINGS:  Engagement | Wedding Bands | Mens

The user than proceeds onto clicking ENGAGEMENT RINGS and now brought to a page with lets say 5 diff E.rings to choose from, they click on a ring called 14KT GOLD BLING RING and its a page with expanded information for 14KT GOLD BLING RING

Database Point of view:
Based on what you said nate, there should be just 1 entire enchilada of a DB with many tables - so to understand what your saying a table called Product should be created with each product given its own ID (meaning Ring=1, Necklace=2) and than 2nd table would be Category which would be "Engagement, Wedding Band, Mens" - and those categories should get an ID - I am confused how all these ID's would be quiered via php....? I am also confused because where do I hold the info for the 14K BLING RING.....?

Thanks again everyone....I have done some of my fair share of DB's but nothing like this before...so pardon my questions :/

[jcbarr]

Normally multiple databases is not the way to go. Several related tables within one database is normally the best way to go. Just make sure they are related to one another simply and understandably.

Read up on database normalization as well. That has helped me tremendously in the recent months.

[chronister]

[b]product table [/b]

product_id |        product_name |      vendor_id |      category_id |    subcat_id |  price |  description 


  1                |  14kt Bling Ring  |      1 |                        3 |              1 |      14.99 |        This ring is a beautiful gift for the one you love | etc 

[b]vendor table:[/b]

vendor_id | vendor_name          | more_vendor_info | more_vendor_info | more_vendor_info
  1                Hearts On Fire  | additional data as you see fit

[b]category table: [/b]
category_id |        category_name |              category_descripton
  1                        Necklaces              Our Necklaces make the perfect stocking stuffer
  2                          Bracelets                description.....
  3                  rings                              description



subcategory table:
subcat_id          |          subcat_name            |              subcat_description

1                                    Engagement Sets                Description here


So you see that the product table contains the actual product itself:

product_id is the 14kt gold bling ring made by hearts on fire and is in category: Rings subcategory: Engagement sets,

You would pretty much start at the top and drill down from there to get to this level.

example:
page1 has link for the vendors & categories.

Query the DB to find all vendors and categories, display a link with thier ID passed in the url e.g.
// set variable for vendor id after the db is queried I like using mysql_fetch_object myself
while ($row=mysql_fetch_object){
$vendor_name=$row->vendor_name;
$vendor_id=$row->vendor_id;
echo '<a href="page2.php?id='.$vendor_id.'" >.'$vendor_name.'</a>  ';

}

This would give you a link list of Vendor 1  Vendor 2  Vendor 3  Vendor 4

or replace the vendor with category, its the same concept.

*clicked on vendor 1*

page2 opens up and inside there you have to get the id you passed in the URL

$id=$_GET['id'];

//query db and get items where vendor_id=$id

you then display those items on the page

This is not an excellent example, but its the general premise and hopefully it helps.

In all honesty, I would be confused as hell if trying to do this myself as well because I am not real skilled in mysql yet, and am spending hours a day trying to learn. But the key to learning it is keep plugging away 1 line at a time. Break it down into smaller chunks. Determine what you want it to do first and then keep coding till you accomplish it, and go on to the next piece

Hope it helps

[phpgreenee]

Hi dpabla
this is not a reply to your question, but simply an observation made when I read your reply #2 above.
YOu are supposing that a user would FIRST select a brand.

If I was looking for jewelery, I (as a customer/user point of view) am not interested in the brand.
I would specifically be looking for Rings or Bracelets or Watches. (I.E. a product CATEGORY)

[dpabla]

Chron - that makes perfect sense....you are the man thanks again for breaking that down in simple form...its the simple things like you said that get overlooked!

I have a question you know how the ID's that you have for product_id  vendor_id etc...do I have to manually enter all the numbers and enter the number of the category for example in the product table manually? Or is there a easier way (probably not :/

Thanks again for your help....this is a mouthful!



[quote author=chronister link=topic=119581.msg489964#msg489964 date=1166755583]
[b]product table [/b]

product_id |        product_name |      vendor_id |      category_id |    subcat_id |  price |  description 


  1                |  14kt Bling Ring  |      1 |                        3 |              1 |      14.99 |        This ring is a beautiful gift for the one you love | etc 

[b]vendor table:[/b]

vendor_id | vendor_name          | more_vendor_info | more_vendor_info | more_vendor_info
  1                Hearts On Fire  | additional data as you see fit

[b]category table: [/b]
category_id |        category_name |              category_descripton
  1                        Necklaces              Our Necklaces make the perfect stocking stuffer
  2                          Bracelets                description.....
  3                  rings                              description



subcategory table:
subcat_id          |          subcat_name            |              subcat_description

1                                    Engagement Sets                Description here


So you see that the product table contains the actual product itself:

product_id is the 14kt gold bling ring made by hearts on fire and is in category: Rings subcategory: Engagement sets,

You would pretty much start at the top and drill down from there to get to this level.

example:
page1 has link for the vendors & categories.

Query the DB to find all vendors and categories, display a link with thier ID passed in the url e.g.
// set variable for vendor id after the db is queried I like using mysql_fetch_object myself
while ($row=mysql_fetch_object){
$vendor_name=$row->vendor_name;
$vendor_id=$row->vendor_id;
echo '<a href="page2.php?id='.$vendor_id.'" >.'$vendor_name.'</a>  ';

}

This would give you a link list of Vendor 1  Vendor 2  Vendor 3  Vendor 4

or replace the vendor with category, its the same concept.

*clicked on vendor 1*

page2 opens up and inside there you have to get the id you passed in the URL

$id=$_GET['id'];

//query db and get items where vendor_id=$id

you then display those items on the page

This is not an excellent example, but its the general premise and hopefully it helps.

In all honesty, I would be confused as hell if trying to do this myself as well because I am not real skilled in mysql yet, and am spending hours a day trying to learn. But the key to learning it is keep plugging away 1 line at a time. Break it down into smaller chunks. Determine what you want it to do first and then keep coding till you accomplish it, and go on to the next piece

Hope it helps
[/quote]

[chronister]

set the id fields to be auto increment. Essentially, the id's mean nothing to you. In most databases that I have seen the Id's are simply for the database to link tables together. You would be concerned about the product names, or vendor names etc, but the id's are simply what you query from the database and use that to pass through the url.

example, I have a database that I have done for my wife to keep track of things like school/library/blockbuster rentals and their due dates, notes, a shopping list and such. When I query the database to retrieve all the info for the library table I use SELECT * FROM library. That returns all the fields from that. I use a while loop to loop through the records and create hyperlinks for them. I do 

[code]
while($row=mysql_fetch_object($result)){
<a href="viewitems.php?id=<?php echo $row->lib_id ?>" ><?php echo $row->name ?></a>
}
[/code]

If there are 5 items, I get 5 hyperlinks with the id being passed through the url, an the name of the item is displayed for clicking.

Then on the next page, I set:
$id=$_GET['id'];

then i can set a query to

[code]SELECT * FROM library WHERE id='$id';[/code]

I can then display any of those records on this same page simply by passing the ID as a variable in the URL.

I hope this makes sense to ya.

Nate