[Resolved] Joining Tables?

airric

I've read the php manual on joining tables, but I just don't get it. I was hoping a few examples might help.

My first question is: is it better or faster to join 3 tables or to make one big database

This what I have:

1 database with item quantities info

1 database with item description info

1 database with item pricing info

all 3 databases have a common itemnumber

each database has 39,000 items with between 10 and 20 fields

So does anyone have any ideas?

Thank you, Airric

thorpe

http://www.mysqlfreaks.com/statements/9.php

airric

Thanks Thorpe that helps! Can you tell me which is faster of better ? joining tables or makng one large table???

bubblenut

I think you mean table rather than database but anyway. Think about the situation in terms of the relationships between the tables. At it's most basic level a relationship can be one of three types, One-to-one, One-to-many or Many-to-many. In most cases one-to-one relationships are not needed as you're placing data in more than one table when this is not necesarry (in some cases this is usefull but we won't go there right now). In all cases many-to-many relationships are impossible, you may have heard of the term "link table" this is a table whose sole purpose is to create two one-to-many relationships out of one many-to-many. In the case of your three tables each is holding data about the product and each is defined by the same key (itemnumber), so for each item in quatities there is one and only one item in description and one and only one item in pricing. This statement can be said the same for the relationships between all the tables so we would say that they all have one-to-one relationships. So, these tables can quite easily be merged together into one table. It does not matter that a table holds data about both pricing and quantities because we will only pull out the information we want in our individual queries.

airric

yes, your right I did mean to say tables. So, either should be fine. Thanks for the feedback.