To make a new table or not to make a new table.

Forum Moderators: open

Message Too Old, No Replies

To make a new table or not to make a new table.

Should I add products to an existing table or create a table of their own.

scraptoft

5:02 pm on Jun 20, 2007 (gmt 0)

I am adding a products section to my website. I already have a table in MYSQL named CONTENT.
Could anyone input their expertise on which would run fastest.

Adding an extra table named PRODUCTS with almost identical
fields as CONTENT
or
add the products to the table CONTENT.

Queries if I create a table named PRODUCTS.
1) SELECT * FROM content WHERE x='$y'
UNION
SELECT * FROM products WHERE x='$y'");

2) SELECT * from CONTENT where type='products' AND parent_id='$parent_id'

Queries if I add products to CONTENT.
1) SELECT * FROM content WHERE x='$y'

if ($title ==!null):{
echo "stuff";
else:
2) SELECT * from PRODUCTS WHERE x='$y'");

3) SELECT * from PRODUCTS where parent_id='$parent_id'

All queries will be used on each page.

If I could supply you with any more information to help you give me better input just ask.

casslin

3:43 am on Jun 21, 2007 (gmt 0)

it depends on how you're using the CONTENT table.. is it being used for other content on the website? or just content for each product?

casslin

3:49 am on Jun 21, 2007 (gmt 0)

I've always had great results by using a table called PRODUCTS and adding 30 different template fields (TEMPLATE_REGION_1 to TEMPLATE_REGION_30)

Then I create different templates for different types of products.. The template all use some or all of the regions..

Basically you're using these template regions to hold data without specifically naming the column..

TEMPLATE_REGION_1 could be the Product Summary for one 'type' of product, and it could be the Warranty Information for another type..

It's more flexible to approach it this way, because you're sure to encounter new requirements for newer products that need to be handled..

Basically you're letting the web page template determine which template regions are used for which part of the page, depending on the type of product..

Does that make sense?

scraptoft

11:09 am on Jun 22, 2007 (gmt 0)

Hi Casslin,
The table CONTENT is used for the article type pages on my website. I use my CMS to generate each page from mysql.

I think I have managed to get my head around your template idea - however it seams alot different to how I have been working - although I will have a go working with it now. Could you show a demo table?

My current design will allow me to retrieve product information by using where parent_id='specificpage_id' - isn't this similar to the template system?

Thanks for your time,

LifeinAsia

3:26 pm on Jun 22, 2007 (gmt 0)

casslin,

So if I understand you correctly, you have 30 fields for templates for each product, even though you may only use a few of them for each product? And the information they contain can be different for each product? That sounds very inefficient as well as a mess trying to keep track of what belongs where, especially if you're dealing with thousands of products. Perhaps I misunderstood?

A much more efficient and logical design would be to make a new table for each type (Warranty, Summary, Dimensions, etc.) and do an OUTER JOIN on the tables based on the parent_id in this case.