Relating tables???

[Jim Morris]

Super general question I know, but here goes...
Is there any basic question I should be asking myself when setting up the relations between tables? Example: A Customers table, and an Orders table. My way of thinking would be that the OrdersID should go in the Customers table as a foreign key. But... this book that I'm reading put's the CustomersID in the Orders table as a foreign key.

Although I know which tables need to relate to each other, deciding which should be the one to "house" the foreign key of the other is not so easy to understand. Any help???

Thanks in advance.:)
Jim

[krycek]

This thread may help you:

http://www.codingforums.com/showthread.php?s=&threadid=11583

I would suggest, your Orders table should contain a CustomerID field which simply stores the customer's unique ID.

The reason is that each customer can place many orders, yet each order can only have one customer. Therefore you need the key to be in the orders table. :)

::] krycek [::

[Jim Morris]

Thanks Krycek, but that link is way over my head. I really just need some basic understanding of how to relate tables together, once I know which tables need to relate. (Which is the main table that has the other tables foreign key in it.). Also, please understand that I have about 2 chapters of a book experience in db programming and MySQL. So, at this point... I am having a hard time tying my shoes!!!

Thanks,
Jim
:)

[krycek]

hehehe ok... I wasn't sure how much help you needed, so I just suggested your relationship and provided the link because it is a current thread on the same topic. :)

OK, down to basics:

For you, you will have to deal with the relationship 'manually' inside your application. That essentially means that MySQL has no idea that there is anything going on.

What we need to do is establish what the link is between your two tables... often an common data field. In this case, the customers table stores details about customers, and the orders table stores data about the orders. Now, you need to know what orders a customer has, and also what customers an order has.

We can define relationships by calling them one-to-many, many-to-one, or many-to-many relationships. If you were using the latter, it would be slightly more complex as we would have to introduce a third table to hold keys. However, thankfully your application only needs a one-to-many relationship, which makes things a bit simpler.

Just in case you haven't figured out what fields are having the affair (lol) it works like this:

Your customers can have many orders, yet each order can only have one customer.

That is a fundamental requirement of your system and so tells us that the relationship is one (customer) to many (orders).

How can we implement this in the database?

Well, I will examine a few fields in each table... I will make them up, so feel free to adapt:

--- customers ---
ID
Forenames
Surname
Address

--- orders ---
ID
CustomerID
Details

(( Before I go any further, you will notice that I use mixed case for fieldnames but lowercase for table names. This is because of a MySQL bug that was around a while ago and gave me some grief; I think it has since been fixed however I stick to that method in case my scripts get used on an earlier, unfixed version. ))

OK, so you have a new customer. You collect all the details (more than I have put there lol) and add to the database. The key I have called 'ID' should be a unique, primary key set to auto-increment. That is important! :) All of your databases need such a key, and I always call it ID (personal preference).

Now, that ID is essentially a customer number. It is unique, and MySQL will not only generate it for you but also make sure that it is unique, so there is not really any need for another customer number.

The same applies to the orders database. When someone makes an order, the order number is the ID field of the orders table, and should auto-increment.

Now to marry the two tables :D lol

The relationship is based around the ID field of the customer, because that is the field in the customers table that is unique. All we have to do to create this relationship is to store the customers -> ID field in the CustomerID field of the orders table, for every order.

So, if customer 5428 makes an order, 5428 will be stored in the CustomerID field of the orders table.

As I said before, MySQL has no idea this is going on. But WE know, and hence our scripts should take advantage of this.

When you look for a customer's details, your SQL will be something like this:

SELECT * FROM customers WHERE ID=

and put the customer ID on the end. So in PHP:

"SELECT * FROM customers WHERE ID='" . $id . "'"

Notice that even though the ID is a number, I put single quotes arround it. That is a good practice :)

So what about the orders? OK:

SELECT * FROM orders WHERE ID=

and put the order ID on the end.

So, you know how to retrieve any ONE customer, and any ONE order (this will only ever return one row, because the ID field in both tables is unique).

Now, what about retrieving the customer information for a particular order?

SELECT * FROM customers WHERE ID=

and this time put the CustomerID of the orders table on the end. Because, that's where we stored the link to the customer.

You have two queries going there, and it is quite simple to use the results side-by-side, also each will return one row: one order, and one customer. Now let's do it the other way round:

SELECT * FROM orders WHERE CustomerID=

and put the ID of the customer on the end.

This time we will get not just one row, but as many rows as there are orders for that customer (it could be null!)

Hopefully you have got the idea of how to define your relationships now... I have kept the SQL simple even though there are other ways (not necessarily any better at this level) of doing it.

Anything else, just ask :thumbsup:

::] krycek [::

[Kiwi]

mySql only allows you to relate tables (in the data definition by the use of foreign keys) in a specific type of table -- innoDB tables.

When you create the table, you need to specify the table type, create the table, index the foreign key and include a foreign key constraint (you can add this as an update query for the data definition as well, but the idea is the same).

To do this, you need a query something like this:CREATE TABLE child (
childid INT,
childparent INT,
INDEX par_ind (childparent),
PRIMARY KEY (childid),
FOREIGN KEY (childparent) REFERENCES parent(parentid)
ON DELETE SET NULL)
TYPE=INNODB;
The CREATE TABLE is much the same as a normal create query. In this case, the child table only has two fields (childid and childparent). You index the childparent (very important to do). You then tell mySql that childparent is a FOREIGN KEY, and should be taken from the table parent, matching the primary key parentid (REFERENCES). ON DELETE is a cascade, to tell you how to handle the deletion of the parent records. If a parent record is deleted, then all the fields in child records that refer to that parent will be set to null (you can, alternatively, cascade the delete -- killing records in the child table if you wish). Finally, the table TYPE is set at the end of the create table query -- this has to be innoDB to recognise foreign keys.

Other mySql table types recognise the foreign key syntax, but don't enforce the rules. This is standard sql syntax.

The alternative is to enforce your integrity at the application level, rather than at the database level. This means that you create a field in the child table that's a compatible datatype with the parent, but that you check that the value is correct and handle the cascade manually. It requires more work at the application level (although a well designed application performs most of these checks on update anyway -- so it's primarily needed when reading/linking records).

In your case, the child will be the order. The parent will be the customer. That means that an order belongs to the customer and there can be more than one order per customer. To find all of the orders for a customer, you need to query the orders table, for the customer id.

[Jim Morris]

Thanks everyone for taking the time to really put some detail in the threads. I think I have enough to start creating my first db.

Thanks again!!!:)
Jim