|
|
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
Navigation:
[Reply to this message]
|