Relating databases

[Bigjohn]

Here is the thing...

I have items stored in multiple tables based on classification.

I want to have unique item numbers.

I want to have a customer table, and a sales table

How do I relate them together?

I can work around the unique item numbers by just using different series - i.e. the first table has numbers starting at 10001, the second at 20001, etc.

But how do I track what a customer has bought with the customer table and the sales table?

Thanks in advance! (detailed examples please....) I'm almost done with my site.. this is the last bit I have to do (along with some mail() stuff...)

John

[jamie]

hi bigjohn,

have a read of this article on database normalisation [phpbuilder.com]

it explains very clearly the concept behind relating data to one another. if you get bored of that article after a while, then search google for those keywords.

it is the fundamental concept behind database use, so the time you spend learning about it is time well spent ;-). this article and others like it helped me enormously.

good luck!

[Bigjohn]

@jamie

I've read that, and re-reading it sheds a bit more light. Thanks.

But I don't at this time want to have to write a complicated add-new-items page.

I've got stuff broken down somewhat:

table- artists: contains artist name and bio (and key)
table- ornith: contains artist, image name, thumb nail name, picture description, price, and availability status.

There are more tables like that for each category of art (but only one table of artists).

So, upon further reading, I'm thinking that my sales have to get logged like this:

item, table, date, customer#

and my customer database then contains the pertanent information on the customer...

Does that sound right?

[jamie]

i'm not sure if i understand exactly what you need but it sounds right.

as long as you have a primary_key for each db table (i use an auto-incrementing field called ID), you can access any of the data in that table using just this primary key value.

if you had one table 'items':

ID ¦ item name ¦ price

and one table for 'customers'

ID ¦ customer_name ¦ address ¦ email

then in the sales table you (over simplified) only need two fields to relate these tables to one another:

items_ID ¦ customers_ID

which refer to the primary key fields (ID) in the two tables above.

the more i use databases, the more i find myself normalising / splitting up tables. the really important table (the one which relates everything to one another) then often contains only primary key references to the other tables - no data in itself. it makes it far easier to maintain, and easier to understand once the basic principle is clear - that's my experience anyway ;-)

HTH