Database design question

Which is the best solution for this...

[jonmayes]

Hi guys,

I'm creating a database for an informational site which features product reviews along with associated merchants selling said products on each review page.

I was wondering what the best way would be to store the associated merchant information for each product:

1) in the same database table as the product details create extra fields for merchant_names, merchant_urls, merchant_prices - each containing an array (and use serialize/unserialize)

2) use a 2 dimensional array instead to store array(array(name, url, price)) in one extra field e.g. merchant_details in the same database table

3) create a separate table for merchant information, using a foreign key for the product ids (except I can't see how this can be done without redundancy...)

each merchant may appear more than once on the website, but the urls stored link directly to the product in question. Therefore the merchant names may be duplicated but the urls & prices will be unique to each product.

What do you think?

[jd01]

I think in this case I would opt for number one with an addition merchant_code. I would then associate merchant_code (a numerical value) with the name of the merchant. Although it appears more information to store and redundant, the reason I would use it is length. You could have a code for 99,999 merchants that is 5 characters or less, while a merchant name will most often be longer for every name.

What this does is shortens merchant based queries to a much smaller 'match' because you can use the number instead of the full name to find the correct vendor.

Also by using the addition col's it eliminates the need for a 'split' on the way out... the URL and price, etc. will be separate, so you can use a simple echo to put the values on the page, where if you store in an array, you will have to split, and then order before you can do anything, like link to the appropriate page, with the stored values.

With the additional cols, you will also have the advantage of indexing the price for selecting/ordering that may be necessary.

If there is reason to belive you will be adding more information about a specific vendor, which needs to be unique from the products (besides name) I would opt for two tables, and index the second on the same basis of merchant_code as I stated for the first example... this will give you the ability of a shorter search again, but this time in a relational setting.

My arguement for the shorter search is simple... for each a-z character there are a possible 26 matches - for each 0-9 character there are a possible 10 matches, so as soon as you get to 'k' in an alphabetical search, you are past any possible numerical matches for the character. (This also does not take into account A-Z caps, which add even more possibilities to alphabetical searches.) Better to store the name and associate it with a number than to try to search the name, where ever possible.

Justin

Edit: Can't type...