Posted by NC on 09/21/05 03:09

goo...@charliefortune.com wrote:
>
> I am using MySQL to keep a database containing tables 'merchants' and
> 'products'.
> My 'products' table has an auto-incrementing id for each record, other
> fields, and a 'merchant' field.

In other words, you decided not to use merchant's product numbering
system? Is this wise?

> My 'merchant' table also has an auto-incrementing id. Is this a
> candidate for a relationship between the two ?

Of course. But I would urge you to reconsider auto-incrementing
product numbers.

> Should I have a php script which reads the user input 'amazon' and does
> a SELECT query on 'merchants' to pull the id for 'amazon', then INSERT
> it into a 'merchant_id' field in table 'products', thereby dispensing
> with the word 'amazon' in the 'products' table in order to improve
> efficiency ?

Most likely, yes.

> I am new to MySQL and am unclear about how far to separate data into
> separate tables.

Read up on normalization. Here's a good place to start:

http://www.samspublishing.com/library/content.asp?b=STY_Sql_24hours&seqNum=32

Basically, you should keep asking yourself the same question over
and over again: "What is this piece of data specific to?" Depending
on the answer, you should include it into a particular table. For
examlpe, if a field is merchant-specific, it belongs in the merchants
table; if it is product-specific, it should be in the products table.

> For example, in a database of clothes, would it make
> sense to have a table of 'colours','sizes','material' ?

Depends... Since you are displaying items from other merchants'
catalogs, you should learn how they describe these items (i.e., what
data they make available) and design your data accordingly.

Cheers,
NC

[Back to original message]