I can't think of a tidy way..... help!

[bobemm]

Hi all.

I can't think of nice method for writing this piece of code, I hope y'all can advise.

I have a db table with products in.

source_id int(20)
name varchar(255)
model varchar(255)
brand varchar(255)

I have hundreds of sources for products. all provide me with data in various XML and CSV formats. The current method of dumping all the different sources products into one products table feels very messy. Basically each source has a field in it's table that maps the column number of the source column to the products column.

e.g.
CSV file contains: Nokia,6600,free line rental,Nokia 6600 on Orange Offer

mapping reference:
1=>4, 2=>3, 4=>2
so:
1=>4 == Nokia=>brand

I'm trying to think of a way to do this where I don't use this mapping technique. It feels wrong. If I alter the products table I want the mapping to maintain the correct position.

Any ideas?

Cheers
Bob.

[kidgeek_dfw]

logical mapping is always good unless its small and a no brainer...
You can always map from your main table to another table but I'd learn to stick with just the facts and keep it a simple structure and use joins...

You have many choices, but I'll tell you how I always think:

3 tables:
product/item table
look_up table
map table

product/item (general info about the item)

item_id | item_name | item_dsc (description) | item_price | and so on...

look_up table (this could be brands, types, and so on)

lu_id | lu_name | lu_dsc (description)

logical mapping table

map_id | item_id | lu_id

If you think about this it makes your database simple...
Until you get into it, you'll never know how scalable the Look_Up Table is..
You can use it to group things, describe things all kinds of craziness...

good luck

KidGeek